Welcome to the SQL screen.
The question is on your right: Identify Funnel Bottlenecks in Onboarding. Read through the requirements and the three 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 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 |