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.
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 |
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 |
| avg_hours_between_first_and_second_ride |
|---|
| 28.50 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique rider identifier |
| rider_name | VARCHAR(100) | Rider full name |
| signup_date | DATE | Date the rider signed up |
| city | VARCHAR(50) | Primary rider city |
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique trip identifier |
| user_id | INT | Rider identifier |
| request_ts | TIMESTAMP | Trip request timestamp |
| trip_status | VARCHAR(20) | Trip lifecycle status |
| product_name | VARCHAR(50) | Uber product type |
{"users":[[3,"Carlos Rivera","2024-01-02","San Francisco"],[1,"Alice Chen","2024-01-01","New York"],[5,"Ethan Brooks","2024-01-09","Chicago"],[2,"Bob Smith","2024-01-03","Los Angeles"],[7,"Grace Kim",null,"Seattle"],[4,"Diana Park","2024-01-04","New York"],[6,"Farah Ali","2024-01-10",null],[8,"Henry Lee","2024-01-11","Boston"],["1","Rosa Garcia","2024-01-06","Austin"],["2","Karen Liu","2024-01-14","San Francisco"],["3","Bob Smith","2024-01-06","Portland"],["4","Leo Martinez","2024-01-11","AtlantOutput[["20.17"]]