Task
Uber wants to understand how quickly new riders come back for a second completed trip. Write a SQL query to calculate the average number of days between a user's first completed ride and second completed ride.
Use only the uber_trips table.
Requirements
- Consider only rows where
trip_status = 'completed'.
- For each
rider_id, identify the first and second completed rides by request_ts.
- Exclude riders with fewer than 2 completed rides.
- Return a single row with the average gap in days, rounded to 2 decimal places.
Table Definition
| column | type | description |
|---|
| trip_id | INT | Unique trip identifier |
| rider_id | INT | Uber rider identifier |
| city_name | VARCHAR(50) | City where the trip was requested |
| request_ts | TIMESTAMP | Trip request timestamp |
| trip_status | VARCHAR(20) | Trip status such as completed or canceled |
| | |
Sample Data
| trip_id | rider_id | city_name | request_ts | trip_status |
|---|
| 1005 | 103 | Chicago | 2024-01-10 18:00:00 | completed |
| 1001 | 101 | San Francisco | 2024-01-01 08:00:00 | completed |
| 1003 | 102 | New York | 2024-01-03 09:00:00 | canceled |
| 1002 | 101 | San Francisco | 2024-01-05 09:30:00 | completed |
| 1009 | 105 | Los Angeles | 2024-01-07 08:00:00 | completed |
| 1010 | 105 | Los Angeles | 2024-01-07 20:00:00 | completed |
| | | | |
Expected Output
| avg_days_between_first_and_second_ride |
|---|
| 2.67 |
