SQL: Users With No Rides in the Most Recent 7-Day Window, Including Non-Riders

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.

rides: one row per ride.

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

rides

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:

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.