[Stratascratch - MEDIUM] Distances Traveled ( 테이블 조인, GROUP BY 없이 집계함수 사용하기, 윈도우 함수 없이 상위 레코드 구하기)

Lyft Interview Question - Distances Traveled

Problem

• Difficulty level : Hard

Table : lyft_rides_log

id	int
user_id	int
distance	int

Table : lyft_users

id	int
name	varchar

top 10 users that have traveled the greatest distance

My Solution

1. LEFT JOIN log <- users to GET NAME of user
2. AGGREGATE the distance by id(user)
3. GROUP BY AGGREGATE -> REMOVE DUPLICATES
4. LIMIT 10 (top 10)

/*
- top 10 users
- id | name | total distance traveled
*/

WITH CTE AS (

-- AGGREGATE - SUM BY USER_ID
    SELECT 
        l.user_id, 
        u.name,    
        SUM(distance) OVER (PARTITION BY user_id) AS `total_distance_traveled`
    FROM lyft_rides_log l

        -- LEFT JOIN : TO GET THE NAME OF USER_ID
    LEFT JOIN lyft_users u
        ON l.user_id = u.id

        -- TO GET THE GREATEST DISTANCE
    ORDER BY 3 DESC

)
SELECT
    user_id, 
    name,
    total_distance_traveled
FROM CTE 

-- REMOVE DUPLICATES
GROUP BY 1,2,3

-- TOP 10 USER RESULTs
LIMIT 10