Your question is Top Rider Percentile by Distance. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given Waymo rider trip data and rider profile data. Write a PostgreSQL query to identify the top 5% of riders by total distance traveled and return each qualifying rider with their average trip duration in minutes. Treat total distance as the sum of all completed trip distances per rider, and average duration as the average of completed trip durations only. If the 5% cutoff falls between riders, include riders whose percentile rank places them in the top 5%.
| Column | Type | Description |
|---|---|---|
| rider_idPK | INT | Unique rider identifier |
| rider_name | VARCHAR(100) | Rider full name |
| city | VARCHAR(50) | Primary city for the rider |
| signup_date | DATE | Date the rider signed up |
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique trip identifier |
| rider_id | INT | Rider who took the trip |
| trip_date | DATE | Date of the trip |
| distance_miles | DECIMAL(8,2) | Distance traveled in miles |
| duration_minutes | INT | Trip duration in minutes |
| trip_status | VARCHAR(20) | Trip completion status |