Interview Guides

Weekly Active SMBs by Month | Dataford Interview Questions - Dataford - Ace your Interview

Weekly Active SMBs by Month

Easy

SQL & Data Manipulation

Asked at 3 companies3AggregationsGroup ByDate Functions

Also asked at

Problem

Task

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.

Table Definition

smb_product_events

Column	Type	Description
event_id	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

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

Expected Output

activity_month	avg_weekly_active_smbs
2024-01	1.50
2024-02	1.00

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

Sample Data

Example 1

Input

{"smb_product_events":[[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","Quic

Output[["2024-01","1.50"],["2024-02","1.00"]]

Problem

Task

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.

Table Definition

smb_product_events

Column	Type	Description
event_id	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

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

Expected Output

activity_month	avg_weekly_active_smbs
2024-01	1.50
2024-02	1.00

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

Sample Data

Example 1

Input

{"smb_product_events":[[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","Quic

Output[["2024-01","1.50"],["2024-02","1.00"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Calculate Monthly Sales Growth by Product CategoryMedium

Weekly Active Purchaser Growth RateMedium

Monthly Sales Trends by CategoryMedium

Next question

Weekly Active SMBs by Month

Easy

SQL & Data Manipulation

Asked at 3 companies3AggregationsGroup ByDate Functions

Also asked at

Problem

Task

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.

Table Definition

smb_product_events

Column	Type	Description
event_id	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

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

Expected Output

activity_month	avg_weekly_active_smbs
2024-01	1.50
2024-02	1.00

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

Sample Data

Example 1

Input

{"smb_product_events":[[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","Quic

Output[["2024-01","1.50"],["2024-02","1.00"]]

Problem

Task

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.

Table Definition

smb_product_events

Column	Type	Description
event_id	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

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

Expected Output

activity_month	avg_weekly_active_smbs
2024-01	1.50
2024-02	1.00

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

Sample Data

Example 1

Input

{"smb_product_events":[[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","Quic

Output[["2024-01","1.50"],["2024-02","1.00"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Calculate Monthly Sales Growth by Product CategoryMedium

Weekly Active Purchaser Growth RateMedium

Monthly Sales Trends by CategoryMedium

Next question