Uber-style Company ChallengeHardVerified answerSQLite live

Rider Retention Analysis

For riders who have taken at least 2 trips, what is the average number of days between consecutive trips?

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

Rider Retention Team is building a churn prediction model and needs the average gap between consecutive rides for returning riders.

Calculate inter-trip gap days per rider using LAG() window function and two CTEs.

Return

  • rider_name (full name)
  • total_trips
  • avg_gap_days (rounded 1 decimal)

Constraints

  • Use only completed trips
  • Return riders with at least two completed trips
  • Measure gaps between consecutive trips for the same rider
  • Order by average gap ascending, then rider ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

trips

  • trip_idINTEGER
  • rider_idINTEGER
  • pickup_datetimeDATETIME
  • trip_statusVARCHAR(20)

riders

  • rider_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

LAG(pickup_datetime) OVER (PARTITION BY rider_id ORDER BY pickup_datetime) gives each trip row the previous trip's timestamp for that rider. Subtract using julianday() for the gap in days. Then aggregate in a second CTE.

Hint 2

CTE trip_gaps: SELECT rider_id, pickup_datetime, LAG(pickup_datetime) OVER (PARTITION BY rider_id ORDER BY pickup_datetime) AS prev_trip_date, julianday(pickup_datetime) - julianday(LAG(...)) AS gap_days FROM trips WHERE completed. CTE rider_gap_stats: WHERE prev IS NOT NULL, AVG gap, COUNT(*)+1. Main: JOIN riders, WHERE total_trips >= 2.

Hint 3

Build one stage for prior-trip timestamps and another for rider-level gap averages, keeping only completed trip sequences.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH trip_gaps AS (SELECT rider_id, pickup_datetime, LAG(pickup_datetime) OVER (PARTITION BY rider_id ORDER BY pickup_datetime, trip_id) AS prev_trip_date, julianday(pickup_datetime) - julianday(LAG(pickup_datetime) OVER (PARTITION BY rider_id ORDER BY pickup_datetime, trip_id)) AS gap_days FROM trips WHERE trip_status = 'completed'), rider_gap_stats AS (SELECT rider_id, COUNT(*) + 1 AS total_trips, ROUND(AVG(gap_days), 1) AS avg_gap_days FROM trip_gaps WHERE prev_trip_date IS NOT NULL GROUP BY rider_id) SELECT r.first_name || ' ' || r.last_name AS rider_name, rgs.total_trips, rgs.avg_gap_days FROM rider_gap_stats rgs INNER JOIN riders r ON rgs.rider_id = r.rider_id WHERE rgs.total_trips >= 2 ORDER BY rgs.avg_gap_days ASC, rgs.rider_id

Why this works

Only Alice Anderson (rider_id 101) has 2 trips — Jan 15 and Jan 16 — giving a 1.2-day gap. COUNT(*)+1 compensates: 2 trips produce 1 gap row, so +1 restores the trip count. LAG's first row has no previous trip and is NULL-filtered.

Success check

1 rider — Alice Anderson (2 trips, 1.2 day avg gap)

Expected result

Use this output to verify values, aliases, ordering, and row count.

rider_nametotal_tripsavg_gap_days
Alice Anderson21.2

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.