Coding questions · Waymo
SQL: Monthly Ride Totals and Average Ratings, Newest Month First
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 asYYYY-MM, for example2024-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_monthdescending.
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_monthis 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.
Monthly Ride Totals and Average Ratings, Newest Month First
Table rides
| column | type | constraints |
|---|---|---|
| ride_id | INTEGER | PRIMARY KEY |
| ride_date | DATE | NOT NULL |
| ride_rating | INTEGER | |
| user_id | INTEGER |
Sample data:
| ride_date | ride_id | ride_rating | user_id |
|---|---|---|---|
| 2024-06-15 | 1107 | 4 | 12 |
| 2023-12-17 | 1012 | NULL | 31 |
| 2025-01-01 | 1161 | 5 | 26 |
| 2024-05-19 | 1098 | 4 | 21 |
| 2023-06-30 | 1003 | 4 | 32 |
| 2024-07-01 | 1119 | 5 | 12 |
| 2024-10-31 | 1140 | 1 | 23 |
| 2024-06-01 | 1105 | NULL | 10 |
| 2023-12-31 | 1015 | 2 | 34 |
| 2024-09-22 | 1133 | NULL | 22 |
| 2024-12-20 | 1152 | 3 | 24 |
| 2024-01-01 | 1021 | 1 | 32 |
| 2025-01-15 | 1164 | NULL | 10 |
| 2024-05-31 | 1101 | 5 | 21 |
| 2023-06-14 | 1001 | 5 | 31 |
| 2024-12-08 | 1148 | 3 | 25 |
| 2024-06-30 | 1110 | 3 | 11 |
| 2024-01-09 | 1026 | 2 | 35 |
| 2024-09-05 | 1130 | NULL | 22 |
| 2025-01-15 | 1166 | 4 | 26 |
| 2023-12-01 | 1009 | 3 | 33 |
| 2024-12-31 | 1155 | 2 | 24 |
| 2024-06-11 | 1106 | 4 | 10 |
| 2025-01-01 | 1160 | 4 | 24 |
| 2024-05-03 | 1094 | 5 | 21 |
| 2025-01-31 | 1170 | 3 | 27 |
Expected output
| ride_month | total_rides | avg_rating |
|---|---|---|
| 2025-01 | 5 | 4 |
| 2024-12 | 3 | 2.67 |
| 2024-10 | 1 | 1 |
| 2024-09 | 2 | NULL |
| 2024-07 | 1 | 5 |
| 2024-06 | 4 | 3.67 |
| 2024-05 | 3 | 4.67 |
| 2024-01 | 2 | 1.5 |
| 2023-12 | 3 | 2.5 |
| 2023-06 | 2 | 4.5 |
Hints
- Two different denominators: the ride count and the rating average are computed over different sets of rows when some ratings are missing.