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.
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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| signup_surface | VARCHAR(50) | Quora surface where signup started |
| country_code | VARCHAR(2) | User country code |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique onboarding event identifier |
| user_id | INT | User who triggered the onboarding event |
| event_name | VARCHAR(50) | Onboarding event type |
| event_time | TIMESTAMP | Timestamp of the onboarding event |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique product event identifier |
| user_id | INT | User who triggered the product event |
| event_name | VARCHAR(50) | Product event type |
| event_time | TIMESTAMP | Timestamp of the product event |
{"quora_users":[[108,"2024-01-10","android","US"],[101,"2024-01-03","web","US"],[111,"2024-02-01","web","US"],[104,"2024-01-05","web",null],[106,"2024-01-08","email_invite","US"],[103,"2024-01-05","ios","GB"],[110,"2024-01-10","","CA"],[102,"2024-01-03","android","IN"],[109,"2024-01-12","ios","IN"],[105,"2024-01-08","web","CA"],[107,"2024-01-08",null,"FR"],[112,"2023-12-31","android","US"],["1","2024-01-03","homepage","IT"],["2","2024-01-11","organic_search","SE"],["3","2024-01-07","referral","NOutput[["2024-01-01","1","0","0","0.00","0.00"],["2024-01-03","3","1","2","33.33","66.67"],["2024-01-05","2","1","0","50.00","0.00"],["2024-01-06","1","0","0","0.00","0.00"],["2024-01-07","1","0","0","0.00","0.00"],["2024-01-08","4","1","1","25.00","25.00"],["2024-01-10","5","1","1","20.00","20.00"],["2024-01-11","1","0","0","0.00","0.00"],["2024-01-12","1","0","1","0.00","100.00"],["2024-01-14","1","0","0","0.00","0.00"],["2024-01-15","1","0","0","0.00","0.00"],["2024-01-19","1","0","0","0.00","0.00"
