SQL: Monthly Ride Totals and Average Ratings, Newest Month First
Company: Waymo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
A ride service stores one row per ride. For every calendar month that has at least one ride, report the total number of rides and the average ride rating, with the most recent month first.
### Input Table
**`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 | Rider's rating of the ride from 1 to 5; NULL if not rated |
| `user_id` | INTEGER | The rider |
### Output Contract
- Write one read-only PostgreSQL query (a single `SELECT`, optionally with CTEs).
- Return one row per calendar month that has at least one ride, with columns:
- `ride_month` (TEXT): the month formatted as `YYYY-MM`, for example `2024-06`;
- `total_rides` (BIGINT): the number of rides in that month, rated or not;
- `avg_rating` (NUMERIC): the average of the non-NULL ratings of that month's rides, rounded to 2 decimal places; NULL if no ride in that month has a rating.
- Order the result by `ride_month` descending.
### Example
`rides`
| ride_id | ride_date | ride_rating | user_id |
|---|---|---|---|
| 1 | 2024-05-03 | 5 | 10 |
| 2 | 2024-05-20 | 4 | 11 |
| 3 | 2024-06-02 | NULL | 10 |
| 4 | 2024-06-15 | 4 | 12 |
| 5 | 2024-06-30 | 3 | 11 |
| 6 | 2024-06-11 | 4 | 10 |
| 7 | 2024-07-01 | 5 | 12 |
Expected result:
| ride_month | total_rides | avg_rating |
|---|---|---|
| 2024-07 | 1 | 5.00 |
| 2024-06 | 4 | 3.67 |
| 2024-05 | 2 | 4.50 |
June has four rides; the unrated one counts toward `total_rides` but not toward the average, which is (4 + 3 + 4) / 3 = 3.666..., rounded to 3.67.
### Constraints and Clarifications
- Months are calendar months of `ride_date`; rides from the same month of different years are different months.
- Months with no rides do not appear.
- Ratings are integers, so compute the average in a non-integer type before rounding; integer division would truncate it.
- `ride_month` is unique per row, so the ordering is total.
```hint Two different denominators
The ride count and the rating average are computed over different sets of rows when some ratings are missing.
```
Overview: Write a PostgreSQL query that reports, for each calendar month, the total number of rides and the average rating rounded to two decimals, newest month first. Tests month bucketing, counting unrated rides while averaging only rated ones, and ordering.
A ride service stores one row per ride. For every calendar month that has at least one ride, report the total number of rides and the average ride rating, with the most recent month first.
### Input Table
**`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 | Rider's rating of the ride from 1 to 5; NULL if not rated |
| `user_id` | INTEGER | The rider |
### Output Contract
- Write one read-only PostgreSQL query (a single `SELECT`, optionally with CTEs).
- Return one row per calendar month that has at least one ride, with columns:
- `ride_month` (TEXT): the month formatted as `YYYY-MM`, for example `2024-06`;
- `total_rides` (BIGINT): the number of rides in that month, rated or not;
- `avg_rating` (NUMERIC): the average of the non-NULL ratings of that month's rides, rounded to 2 decimal places; NULL if no ride in that month has a rating.
- Order the result by `ride_month` descending.
### Constraints and Clarifications
- Months are calendar months of `ride_date`; rides from the same month of different years are different months.
- Months with no rides do not appear.
- Ratings are integers, so compute the average in a non-integer type before rounding; integer division would truncate it.
- `ride_month` is unique per row, so the ordering is total.
Tables
rides(ride_id INTEGER, ride_date DATE, ride_rating INTEGER, user_id INTEGER)
Hints
- Two different denominators: the ride count and the rating average are computed over different sets of rows when some ratings are missing.