[Stratascratch - HARD] Distance Per Dollar (일별 집계와 월별 평균 동시에 구하기, 날짜 컬럼에서 부분 추출하기 )

Uber Interview Question - Distance Per Dollar

Problem

• Difficulty level : Hard

Table : driver_to_client_distance

request_id	int
request_date	datetime
request_status	varchar
distance_to_travel	float
monetary_cost	float
driver_to_client_distance	float

/*
Distance-per-dollar : the distance traveled divided by the cost of the ride.
    - (‘distance_to_travel’) /  (‘monetary_cost’) for each ride

The difference between 
    - the distance-per-dollar for each date and
    - the average distance-per-dollar for that year-month

- count both success and failed request_status
- all dates : unique
*/

output

1. EXTRACT year_month FROM request_date
2. ABSOLUTE avg diff in distance per dollar, round(x,2)
3. ORDER BY request_date (ASC)

My Solution

1. USING CTEs
   1. Distance Per Doller BY DATE
   2. AVG OF Distance Per Doller BY MONTH
2. Extract the YEAR AND MONTH from a date
3. Get the diff of a,b

WITH CTE1 AS (
        -- EXTRACT YEAR-MONTH FROM DATE COLUMN
        -- DISTANCE PER DOLLAR BY DATE
    SELECT 
        request_date,
        EXTRACT(YEAR_MONTH FROM request_date) AS `month`, 
        distance_to_travel/monetary_cost AS `daily`
    FROM uber_request_logs
)

, CTE2 AS (
        -- FIND THE AVERAGE 
    SELECT 
        daily,
        month,
        AVG(daily) OVER (PARTITION BY month) AS `monthly`
    FROM CTE1
)

-- GET THE DIFF
SELECT 
    month,
    ROUND(ABS(daily-monthly), 2) AS `avg_diff`
FROM CTE2

-- GROUP BY :: AGGREGATE THE DUPLIATE COLUMNS
GROUP BY month
ORDER BY 1