Analyze User Ride Activity with SQL
Company: Waymo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Use the following tables to answer four SQL analysis tasks.
```text
rides(ride_id, ride_date, ride_rating, user_id)
users(user_id, city)
```
### Constraints & Assumptions
- Write ANSI-style SQL and state any date functions that depend on your dialect.
- `user_id` is unique in `users`; `ride_id` is unique in `rides`.
- `ride_date` is a timestamp, and `ride_rating` may be null.
- A user with no matching row in `rides` has taken zero rides.
- An active month is a distinct calendar month in which a user completed at least one ride; inactive calendar months do not advance the active-month rank.
### Clarifying Questions to Ask
- Is the seven-day window ending July 7 inclusive, and in which time zone is `ride_date` interpreted?
- Should a null rating be ignored by the database average?
- What output format should represent a calendar month?
- Should cities with no qualifying users appear with a zero count?
### Part 1: Inactive Users in a Seven-Day Window
Count users with zero rides during the inclusive seven-day window ending July 7, 2024. Include users who have never taken a ride.
#### What This Part Should Cover
- A left join or `NOT EXISTS` condition that preserves users without rides.
- Date predicates placed so they do not accidentally turn an outer join into an inner join.
- Clear inclusive-start and exclusive-end boundaries.
### Part 2: Monthly Ride Aggregation
For each calendar month, return the total number of rides and average non-null rating rounded to two decimals. Sort months from newest to oldest.
#### What This Part Should Cover
- Calendar-month truncation, ride-level counting, null-aware averaging, rounding, and descending sort.
### Part 3: Low-Frequency Users by City
For each city, count distinct users who have taken zero or one ride across all available history. Include users who have never taken a ride.
#### What This Part Should Cover
- A user-level ride count built without losing zero-ride users, followed by a city-level aggregation.
- Protection against counting rides rather than qualifying users.
### Part 4: Ratings in the First and Third Active Months
For each city, compute the average ride rating separately for users' first and third active months. The final average must be across all qualifying rides, not an average of user-level or month-level averages.
#### What This Part Should Cover
- Deduplication to one user-month before ranking active months.
- A per-user chronological rank that skips inactive calendar months.
- A join back to ride-level data before calculating the final weighted average.
### What a Strong Answer Covers
- Correct preservation of zero-activity users, explicit date boundaries, and the appropriate aggregation grain for every part.
- Readable CTEs and joins whose cardinality can be explained.
- Awareness of null ratings, time zones, ties at the month grain, and SQL-dialect differences.
### Follow-up Questions
1. How would you return zero for a city with no inactive users in Part 1?
2. What index would help the date-window query?
3. Why is averaging per-user averages incorrect in Part 4?
4. How would you adapt Part 4 to compare first and third active months in separate columns?
Overview: Solve four SQL analytics tasks on ride and user tables: inactive users, monthly rides and ratings, low-frequency users by city, and first-versus-third active-month ratings. The walkthrough emphasizes outer joins, date boundaries, aggregation grain, window functions, and weighted averages.
Community answers
Answer by trancy5.wu
Part 1:
Select
count(u.user_id) as user_ct
from users u
left join rides r on u.user_id = r.user_id and date(r.ride_date) between '2024-07-01' and '2024-07-07'
where r.ride_id is null ;
Part 2:
select
date_trunc('month', ride_date) as calendar_month,
count(ride_id) as rides_ct,
round(avg(ride_rating),2) as avg_rating
from rides
group by date_trunc('month', ride_date)
order by date_trunc('month', ride_date) desc
;
Part 3:
with user_ride_ct as (
select
u.user_id,
u.city,
count(r.ride_id) ride_ct
from users u
left join rides r on u.user_id = r.user_id
group by u.user_id, u.city
)
select
city,
count(user_id) user_ct
from user_ride_ct
where ride_ct <2
group by city
order by 1;
Part 4:
with user_active_months as (
select distinct
user_id,
date_trunc('month', ride_month) active_month
from ride
)
, ranked_active_month as (
select user_id,
active_month,
row_number() over (partition by user_id order by active_month) as active_month_rank
from user_active_months
)
select
u.city,
rm.active_month_rank,
avg(r.ride_rating) as average_rating
from user u
inner join
inner join rides r on ram.user_id = r.user_id and ram.active_month = date_trunc('month', r.ride_date)
inner join user u on u.user_id = rm.user_id
where rm.active_month_rank in (1,3)
group by u.city, rm.active_month_number
order by 1,2;
Answer by sindhujakasula03
Part1:
SELECT user_id
FROM users
WHERE NOT EXISTS (
SELECT 1
FROM rides
WHERE rides.user_id = users.user_id
AND CAST(rides.ride_date AS DATE) BETWEEN '2024-07-07' AND DATE_ADD('2024-07-07', INTERVAL 7 DAY)
);
Answer by sindhujakasula03
Part2
select date_trunc(ride_date, 'month') as ride_month, count(ride_id) as total_rides, round(avg(ride_rating), 2) as avg_rating from rides
group by date_trunc(ride_date, 'month')
order ride_month
Answer by sindhujakasula03
Part 3:
select city, count(user_id) as user_count from
select users.city, users.user_id, coalesce(count(ride_id), 0) as ride_count from users
left join rides on users.user_id = rides.user_id
group by users.user_id, users.city
having ride_count in (0,1)
)
group by city
Answer by juanipnc6
SELECT COUNT(*) AS n
FROM (
SELECT
u.user_id,
COUNT(r.ride_id) AS n_rides
FROM users u
LEFT JOIN rides r
ON u.user_id = r.user_id
AND r.ride_date BETWEEN DATE('2024-07-07') AND DATE_ADD(DATE('2024-07-07'), INTERVAL 7 DAY)
GROUP BY u.user_id
HAVING n_rides = 0
)
SELECT
DATE_FORMAT(ride_date, '%Y-%m') AS ride_month,
COUNT(*) AS n,
ROUND(AVG(ride_rating), 2) AS avg_rating
FROM rides
GROUP BY DATE_FORMAT(ride_date, '%Y-%m')
ORDER BY ride_month DESC;
SELECT
city,
COUNT(*) AS low_frequency_users
FROM (
SELECT
u.city,
u.user_id
FROM users u
LEFT JOIN rides r
ON u.user_id = r.user_id
GROUP BY u.city, u.user_id
HAVING COUNT(r.ride_id) <= 1
) user_counts
GROUP BY city
ORDER BY city;
SELECT
city,
AVG(ride_rating) FILTER (WHERE active_month = 1) AS first_month_avg,
AVG(ride_rating) FILTER (WHERE active_month = 3) AS third_month_avg
FROM (
SELECT
u.city,
r.ride_rating,
m.active_month
FROM users u
JOIN rides r
ON u.user_id = r.user_id
JOIN (
SELECT
user_id,
ride_month,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY ride_month
) AS active_month
FROM (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', ride_date) AS ride_month
FROM rides
) months
) m
ON r.user_id = m.user_id
AND DATE_TRUNC('month', r.ride_date) = m.ride_month
WHERE r.ride_rating IS NOT NULL
) x
WHERE active_month IN (1, 3)
GROUP BY city
ORDER BY city;