Your question is Identify Funnel Bottlenecks in Onboarding. Start with the requirements and the three 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 event data for a Revolut-style customer onboarding funnel. Write a SQL query that returns, for each day in January 2024, the number of users who reached each funnel step, the conversion rate from the previous step, and the estimated number of users lost at that step. Only include users whose acquisition source is Paid Search or Referral, and treat the funnel as ordered from signup to kyc_submitted to kyc_approved to card_ordered.
Your result should help identify the main operational bottleneck by showing where the largest drop happens on each day. If a user triggers the same step multiple times on the same day, count that user only once for that step-day combination.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_source | VARCHAR(50) | Marketing source for the user |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| step_name | VARCHAR(50) | Name of the funnel step |
| event_time | TIMESTAMP | Timestamp of the funnel event |
| Column | Type | Description |
|---|---|---|
| step_namePK | VARCHAR(50) | Name of the funnel step |
| step_rank | INT | Sequential order of the step in the funnel |