Task
Rippling’s growth team wants to identify which user segments convert best through the signup funnel in Rippling HR Cloud. Write a SQL query to find the top converting segments based on users who reached the final funnel step.
Use the tables below to calculate conversion from signup_started to payroll_enabled for each segment.
Requirements
- Consider only users whose
signup_started_at is in January 2024.
- Define a converted user as one who has a
payroll_enabled event within 14 days of signup.
- Group results by
segment_name from the segments table.
- Return these columns:
segment_name, started_users, converted_users, conversion_rate, and segment_rank.
- Only include segments with at least 2 started users.
- Rank segments by
conversion_rate descending, then converted_users descending.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| segment_id | INT | Segment assigned to the user |
| signup_started_at | TIMESTAMP | When the user started signup |
| company_country | VARCHAR(50) | Country of the company |
| | |
segments
| column | type | description |
|---|
| segment_id | INT | Unique segment identifier |
| segment_name | VARCHAR(100) | Segment label |
| acquisition_channel | VARCHAR(50) | Primary acquisition channel |
| | |
funnel_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User tied to the event |
| event_name | VARCHAR(50) | Funnel event name |
| event_time | TIMESTAMP | When the event occurred |
| | |
Sample Data
users
| user_id | segment_id | signup_started_at | company_country |
|---|
| 101 | 1 | 2024-01-03 09:00:00 | US |
| 102 | 1 | 2024-01-08 10:30:00 | CA |
| 103 | 2 | 2024-01-05 12:00:00 | US |
| 104 | 2 | 2024-01-20 08:15:00 | UK |
| | | |
segments
| segment_id | segment_name | acquisition_channel |
|---|
| 1 | SMB Tech | Paid Search |
| 2 | Mid-Market Ops | Sales |
| 3 | Startup Founders | Organic |
| | |
funnel_events
| event_id | user_id | event_name | event_time |
|---|
| 1 | 101 | signup_started | 2024-01-03 09:00:00 |
| 2 | 101 | payroll_enabled | 2024-01-05 14:00:00 |
| 3 | 102 | signup_started | 2024-01-08 10:30:00 |
| 4 | 102 | payroll_enabled | 2024-01-30 09:00:00 |
Expected Output
| segment_name | started_users | converted_users | conversion_rate | segment_rank |
|---|
| Startup Founders | 3 | 2 | 0.6667 | 1 |
| SMB Tech | 3 | 2 | 0.6667 | 2 |
| Mid-Market Ops | 2 | 1 | 0.5000 | 3 |
