Task
Intuit’s Product Growth team wants a simple activity trend for QuickBooks SMBs. Write a SQL query to calculate weekly active SMBs and then summarize those weekly counts at the month level to measure month-over-month growth.
Requirements
- Use only the
smb_product_events table.
- Treat an SMB as active in a week if it has at least one event in that week.
- Return one row per month with:
activity_monthavg_weekly_active_smbs as the average of weekly active SMB counts in that month
- Only include rows where
account_type = 'SMB' and event_name is not NULL.
- Order the result by
activity_month ascending.
Table Definition
| column | type | description |
|---|
| event_id | INT | Unique event row ID |
| smb_id | INT | SMB account identifier |
| account_type | VARCHAR(20) | Account segment such as SMB, Mid-Market, or Enterprise |
| product_name | VARCHAR(50) | Intuit product where the event occurred |
| event_name | VARCHAR(50) | User action recorded for the account |
| event_date | DATE | Date of the event |
Sample Data
| event_id | smb_id | account_type | product_name | event_name | event_date |
|---|
| 8 | 106 | SMB | Mailchimp | campaign_sent | 2024-02-03 |
| 2 | 101 | SMB | QuickBooks Online | invoice_created | 2024-01-03 |
| 11 | 108 | SMB | QuickBooks Online | login | 2024-02-15 |
| 1 | 101 | SMB | QuickBooks Online | login | 2024-01-02 |
| 5 | 103 | SMB | QuickBooks Online | payment_received | 2024-01-10 |
| 9 | 106 | SMB | Mailchimp | NULL | 2024-02-05 |
| 4 | 102 | SMB | QuickBooks Online | login | 2024-01-09 |
| 10 | 107 | Mid-Market | QuickBooks Online | login | 2024-02-06 |
| 3 | 101 | SMB | QuickBooks Online | login | 2024-01-09 |
| 6 | 104 | SMB | TurboTax Business | file_started | 2024-01-16 |
Expected Output
| activity_month | avg_weekly_active_smbs |
|---|
| 2024-01 | 1.50 |
| 2024-02 | 1.00 |
