SQL: Users With No Rides in the Most Recent 7-Day Window, Including Non-Riders
Company: Waymo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
A ride service stores its users and their rides in two tables. Find the **inactive users**: users who took no ride during the most recent 7-day window. Users who registered but have never taken a ride at all are inactive too and must be included.
### 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 | Rider's rating of the ride 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).
- The 7-day window is the 7 calendar days ending on the latest `ride_date` present in `rides`, inclusive: from that date minus 6 days through that date.
- Return one row per inactive user with columns `user_id` and `city`.
- Order the result by `user_id` ascending.
### Example
`users`
| user_id | city |
|---|---|
| 1 | SF |
| 2 | SF |
| 3 | Phoenix |
| 4 | Phoenix |
| 5 | LA |
`rides`
| ride_id | ride_date | ride_rating | user_id |
|---|---|---|---|
| 101 | 2024-07-01 | 5 | 1 |
| 102 | 2024-07-09 | 4 | 2 |
| 103 | 2024-07-14 | NULL | 3 |
| 104 | 2024-07-15 | 3 | 1 |
| 105 | 2024-07-08 | 5 | 4 |
The latest ride date is 2024-07-15, so the window is 2024-07-09 through 2024-07-15. Users 1, 2 and 3 rode inside it (user 2 exactly on the first day). User 4's only ride is one day before the window, and user 5 never rode.
Expected result:
| user_id | city |
|---|---|
| 4 | Phoenix |
| 5 | LA |
### Constraints and Clarifications
- Both window boundaries are inclusive.
- A ride counts toward activity whether or not it has a rating; `ride_rating` plays no part in this question.
- A user with several rides in the window appears nowhere in the output; each inactive user appears exactly once.
- If `rides` is empty, there is no window and no ride, so every user is inactive.
- `user_id` is unique in `users`, so the ordering is total.
```hint Keep the users who never rode
Check whether your join or filter silently drops users who have no rows in `rides`, or whose only rides fall outside the window.
```
Overview: Write a PostgreSQL query that lists users with no rides in the most recent 7-day window, including users who never rode. Tests anchoring a date window to the data, inclusive boundaries, and anti-join logic that keeps users without matching rides.