Coding questions · Waymo

SQL: Count Users With Zero or One Rides per City

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.

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.

Count Users With Zero or One Rides per City

Table `users`

column	type	constraints
user_id	INTEGER	PRIMARY KEY
city	TEXT	NOT NULL

Sample data:

city	user_id
Austin	101
Boston	105
Chicago	108
Denver	110
Seattle	113
Austin	102
Boston	106
Denver	111
Seattle	114
Austin	103
Chicago	109
Boston	107
Denver	112
Seattle	115
Austin	104

Table `rides`

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

Sample data:

ride_date	ride_id	ride_rating	user_id
2024-07-01	1	5	102
2024-07-01	2	4	103
2024-07-01	3	5	107
2024-07-01	4	4	115
2024-07-01	5	5	115
2024-07-02	6	NULL	104
2024-07-02	7	4	105
2024-07-02	8	5	107
2024-07-02	9	1	113
2024-07-03	10	NULL	103
2024-07-03	11	3	110
2024-07-04	12	5	106
2024-07-04	13	4	106
2024-07-04	14	NULL	115
2024-07-05	15	NULL	105
2024-07-05	16	5	111
2024-07-06	17	4	107
2024-07-06	18	NULL	112
2024-07-07	19	4	111
2024-07-07	20	3	115
2024-07-08	21	3	103
2024-07-09	22	2	107
2024-07-09	23	4	115
2024-07-10	24	NULL	112

Expected output

city	low_frequency_users
Austin	3
Chicago	2
Denver	1
Seattle	2

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.