Coding questions · Waymo
SQL: Average Ride Rating in Users' First and Third Active Months by City
Write a PostgreSQL query that compares average ride ratings in each user's first and third active months, grouped by city and averaged over individual rides. Tests ranking distinct active months per user, window functions, NULL ratings and weighting.
A ride service wants to know whether riders rate their rides differently as they keep using the service. For each city, compute the average ride rating in users' 1st active month and in their 3rd active month.
A user's active months are the calendar months in which that user took at least one ride, numbered in chronological order: the earliest such month is the user's 1st active month, the next one the 2nd, and so on. Months without rides are skipped, so the 3rd active month is not necessarily two calendar months after the 1st.
Input Tables
users: one row per registered user.
| Column | Type | Meaning |
|---|---|---|
user_id | INTEGER | Primary key |
city | TEXT | City the user belongs to; never NULL |
rides: one row per ride.
| Column | Type | Meaning |
|---|---|---|
ride_id | INTEGER | Primary key |
ride_date | DATE | Date of the ride; never NULL |
ride_rating | INTEGER | Rating from 1 to 5; NULL if not rated |
user_id | INTEGER | The rider; always references an existing users.user_id |
Output Contract
- Write one read-only PostgreSQL query (a single
SELECT, optionally with CTEs). - Return one row per city that has at least one ride, with columns:
city(TEXT);month_1_avg_rating(NUMERIC): the averageride_ratingover all rides that fall in their rider's 1st active month, for riders in that city, rounded to 2 decimal places;month_3_avg_rating(NUMERIC): the same for rides in their rider's 3rd active month, rounded to 2 decimal places; NULL if no such rated ride exists in that city.
- The averages are taken over individual rides, not over per-user averages: a user with three rides in the month contributes three values.
- Order the result by
cityascending.
Example
users
| user_id | city |
|---|---|
| 1 | SF |
| 2 | SF |
| 3 | LA |
rides
| ride_id | ride_date | ride_rating | user_id |
|---|---|---|---|
| 1 | 2024-01-05 | 5 | 1 |
| 2 | 2024-01-20 | 5 | 1 |
| 3 | 2024-01-31 | 2 | 1 |
| 4 | 2024-02-10 | 4 | 1 |
| 5 | 2024-04-02 | 2 | 1 |
| 6 | 2024-04-18 | NULL | 1 |
| 7 | 2024-02-03 | 2 | 2 |
| 8 | 2024-03-09 | NULL | 2 |
| 9 | 2024-05-01 | 3 | 2 |
| 10 | 2024-03-15 | 5 | 3 |
| 11 | 2024-03-28 | 4 | 3 |
- User 1's active months are January (1st), February (2nd) and April (3rd).
- User 2's are February (1st), March (2nd, a single unrated ride, which still makes the month active) and May (3rd).
- User 3 has only March (1st).
- SF, 1st active month: ratings 5, 5, 2 (user 1) and 2 (user 2), average 14 / 4 = 3.50. (The average of the two users' averages would be 3.00, which is not what is asked.)
- SF, 3rd active month: ratings 2 (user 1; the unrated ride is ignored) and 3 (user 2), average 2.50.
- LA: 1st active month average (5 + 4) / 2 = 4.50; no 3rd active month.
Expected result:
| city | month_1_avg_rating | month_3_avg_rating |
|---|---|---|
| LA | 4.50 | NULL |
| SF | 3.50 | 2.50 |
Constraints and Clarifications
- A calendar month is identified by its year and month; rides in the same month of different years are different months.
- Every ride, rated or not, counts when deciding which months are active. Unrated rides are then left out of the averages.
- Users without a 3rd active month simply contribute nothing to
month_3_avg_rating; they are still included inmonth_1_avg_rating. - If all of a city's 1st-active-month rides are unrated,
month_1_avg_ratingis NULL. - Users with no rides do not affect the result, and a city whose users have no rides does not appear.
cityis unique per output row, so the ordering is total.
Hint: Number the months, not the rides
Rank each user's distinct active months before looking at any rating.
Average Ride Rating in Users' First and Third Active Months by City
Table users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY |
| city | TEXT | NOT NULL |
Sample data:
| city | user_id |
|---|---|
| San Francisco | 1 |
| San Francisco | 2 |
| Los Angeles | 3 |
| Los Angeles | 4 |
| Chicago | 5 |
| Denver | 6 |
| Denver | 7 |
| Austin | 8 |
| San Francisco | 9 |
| Chicago | 10 |
Table rides
| column | type | constraints |
|---|---|---|
| ride_id | INTEGER | PRIMARY KEY |
| ride_date | DATE | NOT NULL |
| ride_rating | INTEGER | |
| user_id | INTEGER | NOT NULL |
Sample data:
| ride_date | ride_id | ride_rating | user_id |
|---|---|---|---|
| 2023-01-10 | 1 | 4 | 2 |
| 2024-01-08 | 2 | 2 | 2 |
| 2023-06-20 | 3 | 5 | 2 |
| 2024-01-25 | 4 | NULL | 2 |
| 2024-02-14 | 5 | 3 | 2 |
| 2023-11-12 | 6 | 4 | 1 |
| 2023-11-12 | 7 | 2 | 1 |
| 2023-11-30 | 8 | 5 | 1 |
| 2023-12-01 | 9 | NULL | 1 |
| 2024-01-15 | 10 | 4 | 1 |
| 2024-01-31 | 11 | 1 | 1 |
| 2024-03-05 | 12 | 5 | 1 |
| 2024-02-29 | 13 | 3 | 3 |
| 2024-03-01 | 14 | 4 | 3 |
| 2024-07-04 | 15 | 5 | 3 |
| 2024-07-19 | 16 | 5 | 3 |
| 2024-08-02 | 17 | 1 | 3 |
| 2024-05-10 | 18 | 3 | 4 |
| 2024-05-10 | 19 | 4 | 4 |
| 2024-06-01 | 20 | NULL | 4 |
| 2023-10-02 | 21 | 1 | 5 |
| 2023-10-03 | 22 | 2 | 5 |
| 2023-10-05 | 23 | 2 | 5 |
| 2023-10-09 | 24 | NULL | 5 |
| 2023-10-11 | 25 | 2 | 5 |
| 2023-10-16 | 26 | 3 | 5 |
| 2023-10-20 | 27 | 3 | 5 |
| 2023-10-24 | 28 | 2 | 5 |
| 2023-10-31 | 29 | 2 | 5 |
| 2023-12-24 | 30 | 4 | 5 |
| 2024-02-11 | 31 | NULL | 5 |
| 2024-04-01 | 32 | 5 | 5 |
| 2024-03-31 | 33 | NULL | 6 |
| 2024-04-01 | 34 | 5 | 6 |
| 2024-04-15 | 35 | NULL | 7 |
| 2024-04-15 | 36 | NULL | 7 |
Expected output
| city | month_1_avg_rating | month_3_avg_rating |
|---|---|---|
| Chicago | 2.13 | NULL |
| Denver | NULL | NULL |
| Los Angeles | 3.33 | 5 |
| San Francisco | 3.75 | 2.33 |
Hints
- Number the months, not the rides: rank each user's distinct active months before looking at any rating.
- Every ride, rated or not, counts when deciding which months are active; unrated rides are only left out of the averages.