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.

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.

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

Table users

column	type	constraints
user_id	INTEGER	PRIMARY KEY
city	TEXT	NOT NULL

Sample data:

city	user_id
Phoenix	107
SF	101
LA	104
Phoenix	110
SF	102
Seattle	109
Phoenix	103
LA	105
SF	108
Seattle	106

Table rides

column	type	constraints
ride_id	INTEGER	PRIMARY KEY
ride_date	DATE	NOT NULL
ride_rating	INTEGER
user_id	INTEGER	NOT NULL

Sample data:

ride_date	ride_id	ride_rating	user_id
2024-02-26	7003	4	101
2024-03-03	7011	3	103
2024-01-15	7001	4	106
2024-02-29	7008	NULL	105
2024-02-12	7005	2	107
2024-02-03	7002	5	106
2024-02-27	7013	5	108
2024-02-20	7006	3	106
2024-03-01	7009	5	107
2023-12-28	7004	NULL	110
2024-03-02	7012	4	108
2024-01-31	7007	4	109
2024-03-03	7010	NULL	108
2024-02-25	7014	5	102

Expected output

user_id	city
102	SF
104	LA
106	Seattle
109	Seattle
110	Phoenix

Hints

1. 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.
2. The window ends on the latest ride_date present in rides, not on today's date, and both of its ends are inclusive.