Weekly Active SMBs by Month | Dataford Interview Questions

Intuit wants a simple monthly view of SMB engagement across product events. Write a SQL query to calculate the average number of active SMBs per active week in each month.

Requirements

Use only rows where account_type = 'SMB'.
Ignore rows where event_name is NULL.
Count distinct smb_id values as active SMBs.
For each month, divide the distinct active SMB count by the number of distinct week buckets in that month.
Return the month formatted as YYYY-MM and the average rounded to 2 decimal places.
Order the result by month ascending.

Schema

smb_product_events

Column	Type	Description
event_idPK	INT	Unique event row identifier
smb_id	INT	SMB account identifier
account_type	VARCHAR(20)	Customer segment
product_name	VARCHAR(50)	Product where the event occurred
event_name	VARCHAR(50)	Tracked user action
event_date	DATE	Date the event occurred

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the one table are in the Question tab.

Problem

Intuit wants a simple monthly view of SMB engagement across product events. Write a SQL query to calculate the average number of active SMBs per active week in each month.

Requirements

Use only rows where account_type = 'SMB'.
Ignore rows where event_name is NULL.
Count distinct smb_id values as active SMBs.
For each month, divide the distinct active SMB count by the number of distinct week buckets in that month.
Return the month formatted as YYYY-MM and the average rounded to 2 decimal places.
Order the result by month ascending.

Schema

smb_product_events

Column	Type	Description
event_idPK	INT	Unique event row identifier
smb_id	INT	SMB account identifier
account_type	VARCHAR(20)	Customer segment
product_name	VARCHAR(50)	Product where the event occurred
event_name	VARCHAR(50)	Tracked user action
event_date	DATE	Date the event occurred

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the one table are in the Question tab.