Welcome to the SQL screen.
The question is on your right: Top Rider Percentile by Distance. Read through the requirements and the two tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |