Task
At Lyft, an Operations Manager may use a pivot-style summary to see where a market is breaking down across the ride funnel. Write a SQL query to summarize funnel volume by market and pivot funnel_stage into separate columns.
Requirements
- Return one row per
market for records on event_date = '2024-06-01'.
- Create pivoted columns for total
sessions, ride_requests, matched_rides, and completed_rides using conditional aggregation.
- Include only markets with at least 1 completed ride.
- Order the result by
completed_rides ascending, then market ascending.
Table Definition
lyft_market_funnel
| column | type | description |
|---|
| id | INT | Primary key for each funnel record |
| market | VARCHAR(50) | Lyft market name |
| event_date | DATE | Date of the funnel measurement |
| funnel_stage | VARCHAR(30) | Funnel step being measured |
| stage_count | INT | Count recorded for that stage |
| note | VARCHAR(100) | Optional annotation from operations |
Sample Data
| id | market | event_date | funnel_stage | stage_count | note |
|---|
| 1 | Austin | 2024-06-01 | sessions | 1200 | normal day |
| 2 | Austin | 2024-06-01 | ride_requests | 300 | normal day |
| 3 | Austin | 2024-06-01 | matched_rides | 240 | normal day |
| 4 | Austin | 2024-06-01 | completed_rides | 210 | normal day |
| 5 | Miami | 2024-06-01 | sessions | 1500 | weather impact |
| 6 | Miami | 2024-06-01 | ride_requests | 330 | weather impact |
Expected Output
| market | sessions | ride_requests | matched_rides | completed_rides |
|---|
| Nashville | 900 | 180 | 90 | 60 |
| Miami | 1500 | 330 | 200 | 150 |
| Austin | 1200 | 300 | 240 | 210 |
