SQL: Count Users With Zero or One Rides per City
Company: Waymo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
A ride service stores its users and their rides. A **low-frequency user** is a user whose total number of rides is 0 or 1. Count the low-frequency users in each city. Users who registered but never took a ride have 0 rides and must be counted.
### 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 |
| `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 low-frequency user, with columns:
- `city` (TEXT);
- `low_frequency_users` (BIGINT): the number of users in that city with 0 or 1 rides in total.
- Order the result by `city` ascending.
### Example
`users`
| user_id | city |
|---|---|
| 1 | SF |
| 2 | SF |
| 3 | SF |
| 4 | LA |
| 5 | LA |
| 6 | Phoenix |
`rides`
| ride_id | ride_date | ride_rating | user_id |
|---|---|---|---|
| 1 | 2024-07-01 | 5 | 1 |
| 2 | 2024-07-03 | 4 | 1 |
| 3 | 2024-07-04 | NULL | 2 |
| 4 | 2024-07-05 | 3 | 4 |
| 5 | 2024-07-06 | 5 | 5 |
| 6 | 2024-07-07 | 4 | 5 |
| 7 | 2024-07-08 | 5 | 5 |
| 8 | 2024-07-09 | 2 | 6 |
| 9 | 2024-07-10 | 4 | 6 |
Ride totals: user 1 has 2, user 2 has 1, user 3 has 0, user 4 has 1, user 5 has 3, user 6 has 2. The low-frequency users are 2 and 3 (SF) and 4 (LA). Phoenix has none, so it does not appear.
Expected result:
| city | low_frequency_users |
|---|---|
| LA | 1 |
| SF | 2 |
### Constraints and Clarifications
- A ride counts toward the total whether or not it is rated.
- Cities with no low-frequency user are omitted rather than shown with 0.
- Each user belongs to exactly one city and is counted at most once.
- `city` is unique per output row, so the ordering is total.
```hint Zero is a count too
Make sure a user with no rides produces a total of 0 rather than disappearing from the data before you filter.
```
Overview: Write a PostgreSQL query that counts, per city, the users who have taken zero or one ride in total, including users who never rode. Tests outer joins or subqueries that preserve zero counts, grouping by city, and filtering on aggregated totals.