Task
You are given rider and trip data from a ride-hailing app. Write a PostgreSQL query to calculate the average time between each user's first completed ride and second completed ride. Only include users who have at least two completed rides, and return the average as hours rounded to 2 decimal places.
Schema
users
| column | type | description |
|---|
| user_id | INT | Unique rider identifier |
| rider_name | VARCHAR(100) | Rider name |
| signup_date | DATE | Account creation date |
| city | VARCHAR(50) | Home city |
uber_trips
| column | type | description |
|---|
| trip_id | INT | Unique trip identifier |
| user_id | INT | Rider identifier |
| request_ts | TIMESTAMP | Time the trip was requested |
| trip_status | VARCHAR(20) | Trip status such as completed or canceled |
| product_name | VARCHAR(50) | Uber product used |
Sample data
uber_trips
| trip_id | user_id | request_ts | trip_status | product_name |
|---|
| 101 | 3 | 2024-01-10 08:00:00 | completed | UberX |
| 102 | 1 | 2024-01-03 09:00:00 | completed | UberX |
| 103 | 2 | 2024-01-05 10:00:00 | canceled | UberXL |
| 104 | 1 | 2024-01-04 09:30:00 | completed | UberX |
| 105 | 4 | 2024-01-08 18:00:00 | completed | Uber Black |
Expected output
| avg_hours_between_first_and_second_ride |
|---|
| 28.50 |
