Pivot Lyft Funnel Breakdown | Dataford Interview Questions

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.

Schema

lyft_market_funnel

Column	Type	Description
idPK	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

Your question is Pivot Lyft Funnel Breakdown. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

Schema

lyft_market_funnel

Column	Type	Description
idPK	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

Your question is Pivot Lyft Funnel Breakdown. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.