Task
You are given user signup data and downstream product events from Quora. Write a PostgreSQL query that produces a funnel by signup date for users who signed up in January 2024. Treat a user as activated if they perform at least one follow_topic, upvote, or ask_question event within 7 days after signup, inclusive of the signup day. Return, for each signup date, the number of signed-up users, the number who completed onboarding, the number who activated, and the onboarding and activation rates as percentages of signups.
Use the event log rather than assuming onboarding or activation from a status field, and make sure each user is counted at most once per stage.
Schema
quora_users
| column | type | description |
|---|
| user_id | INT | Unique user ID |
| signup_date | DATE | Date the user signed up |
| signup_surface | VARCHAR(50) | Signup entry point |
| country_code | VARCHAR(2) | User country |
| | |
quora_onboarding_events
| column | type | description |
|---|
| event_id | INT | Unique onboarding event ID |
| user_id | INT | User who triggered the event |
| event_name | VARCHAR(50) | Onboarding event type |
| event_time | TIMESTAMP | Event timestamp |
| | |
quora_product_events
| column | type | description |
|---|
| event_id | INT | Unique product event ID |
| user_id | INT | User who triggered the event |
| event_name | VARCHAR(50) | Product event type |
| event_time | TIMESTAMP | Event timestamp |
Sample data
| user_id | signup_date | signup_surface | country_code |
|---|
| 101 | 2024-01-03 | web | US |
| 102 | 2024-01-03 | android | IN |
| 103 | 2024-01-05 | ios | GB |
| event_id | user_id | event_name | event_time |
|---|
| 1 | 101 | complete_onboarding | 2024-01-03 09:10:00 |
| 101 | follow_topic | 2024-01-04 12:00:00 | |
| 102 | complete_onboarding | 2024-01-12 08:00:00 | |
Expected output
| signup_date | signups | onboarded_users | activated_users | onboarding_rate_pct | activation_rate_pct |
|---|
| 2024-01-03 | 2 | 1 | 2 | 50.00 | 100.00 |
| 2024-01-05 | 2 | 1 | 1 | 50.00 | 50.00 |
