Your question is SQL: Rides and Weekly Drivers. 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.
Assume that we have the following tables, with columns as indicated: Rides ride_id start_time end_time passenger_id driver_id ride_region is_completed (Y/N) Drivers driver_id onboarding_time home_region
Write a query that we could use to create a plot of the total count of rides completed in our San Francisco region, in each week over the last 12 weeks.
For each week over the last 12 weeks, write a query that will return a count of the total drivers who gave at least one ride. And only for driver’s based in San Francisco.
Use start_time to assign rides to weeks and include weeks with zero activity.
week_start, metric_name, and metric_value, with one row per metric per week.week_start and then metric_name.| Column | Type | Description |
|---|---|---|
| ride_idPK | INT | Unique ride identifier |
| start_time | TIMESTAMP | Ride start timestamp |
| end_time | TIMESTAMP | Ride end timestamp |
| passenger_id | INT | Passenger identifier |
| driver_id | INT | Driver identifier referencing drivers.driver_id |
| ride_region | VARCHAR(100) | Region where the ride occurred |
| is_completed | VARCHAR(1) | Whether the ride completed, Y or N |
| Column | Type | Description |
|---|---|---|
| driver_idPK | INT | Unique driver identifier |
| onboarding_time | TIMESTAMP | Timestamp when the driver completed onboarding |
| home_region | VARCHAR(100) | Driver's home region |