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.
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.
### 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.
Tables
users(user_id INTEGER, city TEXT)
rides(ride_id INTEGER, ride_date DATE, ride_rating INTEGER, user_id INTEGER)
Hints
- 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.
- A ride counts toward a user's total whether or not it is rated.
Community answers
Answer by Paramartha Sengupta
with ride_db as
(
select a.user_id,a.city, count(distinct b.ride_id) as ride_counts
from users as a
left join rides as b on a.user_id=b.user_id
group by a.user_id,a.city
)
select city, count(distinct user_id) as low_frequency_users
from ride_db
where ride_counts<=1
group by city