SQL: Average Ride Rating in Users' First and Third Active Months by City
Company: Waymo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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 average `ride_rating` over **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 `city` ascending.
### 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 in `month_1_avg_rating`.
- If all of a city's 1st-active-month rides are unrated, `month_1_avg_rating` is NULL.
- Users with no rides do not affect the result, and a city whose users have no rides does not appear.
- `city` is 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.
```
Overview: 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.