Weekly Active Purchaser Growth Rate

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsRunning TotalsDate Functions

Also asked at

Problem

Task

ShopWave wants to track how its active purchaser base changes week over week. Write a PostgreSQL query to calculate the weekly count of active purchasers and the week-over-week growth rate.

An active purchaser is a user who has at least one completed transaction in a given calendar week. Use the transaction date to assign each purchase to a week.

Requirements

Count distinct purchasers per week using only transactions with status = 'completed'.
Return one row per week, even if there are zero active purchasers that week.
Calculate the prior week's active purchaser count.
Compute week-over-week growth rate as: ((current_week_active_purchasers - previous_week_active_purchasers) / previous_week_active_purchasers) * 100 Return NULL when the previous week is 0 or missing.
Order the output by week start date ascending.

Table Definitions

`transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Purchasing user
transaction_date	DATE	Date of the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
store_id	INT	Store where the purchase happened

`users`

column	type	description
user_id	INT	Unique user ID
user_name	VARCHAR(100)	User name
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

Sample Data

`transactions`

transaction_id	user_id	transaction_date	amount	status	store_id
101	1	2024-01-02	45.00	completed	10
102	1	2024-01-03	20.00	completed	10
103	2	2024-01-04	60.00	completed	11
104	3	2024-01-09	30.00	completed	10
105	2	2024-01-10	25.00	failed	11
106	4	2024-01-16	80.00	completed	12
107	4	2024-01-17	10.00	completed	12
108	5	2024-01-23	50.00	pending	10
109	6	2024-01-24	70.00	completed	11
110	NULL	2024-01-25	15.00	completed	11

`users`

user_id	user_name	signup_date	country
1	Ava Chen	2023-12-15	US
2	Liam Patel	2023-12-20	CA
3	Noah Kim	2024-01-05	US
4	Emma Lopez	2024-01-08	UK
5	Mia Johnson	2024-01-12	US
6	Ethan Brown	2024-01-18	CA
7	Sophia Davis	2024-01-22	UK
8	Lucas Martin	2024-01-28	NULL

Expected Output

week_start	active_purchasers	previous_week_active_purchasers	wow_growth_rate_pct
2024-01-01	2	NULL	NULL
2024-01-08	1	2	-50.00
2024-01-15	1	1	0.00
2024-01-22	1	1	0.00

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User who made the transaction
transaction_date	DATE	Date the transaction occurred
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status
store_id	INT	Store identifier

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User full name
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

Sample Data

Example 1

Input

{"users":[[3,"Noah Kim","2024-01-05","US"],[1,"Ava Chen","2023-12-15","US"],[8,"Lucas Martin","2024-01-28",null],[6,"Ethan Brown","2024-01-18","CA"],[2,"Liam Patel","2023-12-20","CA"],[5,"Mia Johnson","2024-01-12","US"],[7,"Sophia Davis","2024-01-22","UK"],[4,"Emma Lopez","2024-01-08","UK"],["1","Elijah Baker","2024-01-20","AU"],["2","James Allen","2023-12-19","NZ"],["3","James Allen","2024-01-25","CL"],["4","Abigail Perez","2024-01-08","null"],["5","Emily Campbell","2023-12-29","ES"],["6","Emil

Output

[["2023-12-25","0","null","null"],["2024-01-01","2","0","null"],["2024-01-08","1","2","-50.00"],["2024-01-15","1","1","0.00"],["2024-01-22","1","1","0.00"],["2024-01-29","1","1","0.00"]]

Problem

Task

ShopWave wants to track how its active purchaser base changes week over week. Write a PostgreSQL query to calculate the weekly count of active purchasers and the week-over-week growth rate.

An active purchaser is a user who has at least one completed transaction in a given calendar week. Use the transaction date to assign each purchase to a week.

Requirements

Count distinct purchasers per week using only transactions with status = 'completed'.
Return one row per week, even if there are zero active purchasers that week.
Calculate the prior week's active purchaser count.
Compute week-over-week growth rate as: ((current_week_active_purchasers - previous_week_active_purchasers) / previous_week_active_purchasers) * 100 Return NULL when the previous week is 0 or missing.
Order the output by week start date ascending.

Table Definitions

`transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Purchasing user
transaction_date	DATE	Date of the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
store_id	INT	Store where the purchase happened

`users`

column	type	description
user_id	INT	Unique user ID
user_name	VARCHAR(100)	User name
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

Sample Data

`transactions`

transaction_id	user_id	transaction_date	amount	status	store_id
101	1	2024-01-02	45.00	completed	10
102	1	2024-01-03	20.00	completed	10
103	2	2024-01-04	60.00	completed	11
104	3	2024-01-09	30.00	completed	10
105	2	2024-01-10	25.00	failed	11
106	4	2024-01-16	80.00	completed	12
107	4	2024-01-17	10.00	completed	12
108	5	2024-01-23	50.00	pending	10
109	6	2024-01-24	70.00	completed	11
110	NULL	2024-01-25	15.00	completed	11

`users`

user_id	user_name	signup_date	country
1	Ava Chen	2023-12-15	US
2	Liam Patel	2023-12-20	CA
3	Noah Kim	2024-01-05	US
4	Emma Lopez	2024-01-08	UK
5	Mia Johnson	2024-01-12	US
6	Ethan Brown	2024-01-18	CA
7	Sophia Davis	2024-01-22	UK
8	Lucas Martin	2024-01-28	NULL

Expected Output

week_start	active_purchasers	previous_week_active_purchasers	wow_growth_rate_pct
2024-01-01	2	NULL	NULL
2024-01-08	1	2	-50.00
2024-01-15	1	1	0.00
2024-01-22	1	1	0.00

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User who made the transaction
transaction_date	DATE	Date the transaction occurred
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status
store_id	INT	Store identifier

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User full name
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country

Sample Data

Example 1

Input

{"users":[[3,"Noah Kim","2024-01-05","US"],[1,"Ava Chen","2023-12-15","US"],[8,"Lucas Martin","2024-01-28",null],[6,"Ethan Brown","2024-01-18","CA"],[2,"Liam Patel","2023-12-20","CA"],[5,"Mia Johnson","2024-01-12","US"],[7,"Sophia Davis","2024-01-22","UK"],[4,"Emma Lopez","2024-01-08","UK"],["1","Elijah Baker","2024-01-20","AU"],["2","James Allen","2023-12-19","NZ"],["3","James Allen","2024-01-25","CL"],["4","Abigail Perez","2024-01-08","null"],["5","Emily Campbell","2023-12-29","ES"],["6","Emil

Output

[["2023-12-25","0","null","null"],["2024-01-01","2","0","null"],["2024-01-08","1","2","-50.00"],["2024-01-15","1","1","0.00"],["2024-01-22","1","1","0.00"],["2024-01-29","1","1","0.00"]]

Practice SQL

PostgreSQL

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

Up next

AWeekly Shopper Retention RateMedium

Compare Weekly User Activity TrendsMedium

Weekly Active SMBs by MonthEasy

Next question

Task

ShopWave wants to track how its active purchaser base changes week over week. Write a PostgreSQL query to calculate the weekly count of active purchasers and the week-over-week growth rate.

An active purchaser is a user who has at least one completed transaction in a given calendar week. Use the transaction date to assign each purchase to a week.

Requirements

Count distinct purchasers per week using only transactions with status = 'completed'.
Return one row per week, even if there are zero active purchasers that week.
Calculate the prior week's active purchaser count.
Compute week-over-week growth rate as: ((current_week_active_purchasers - previous_week_active_purchasers) / previous_week_active_purchasers) * 100 Return NULL when the previous week is 0 or missing.
Order the output by week start date ascending.

Table Definitions

`transactions`

column	type	description
transaction_id	INT	Unique transaction ID
user_id	INT	Purchasing user
transaction_date	DATE	Date of the transaction
amount	DECIMAL(10,2)	Transaction amount
status	VARCHAR(20)	Transaction status
store_id	INT	Store where the purchase happened

`users`

column	type	description
user_id	INT	Unique user ID
user_name	VARCHAR(100)	User name
signup_date	DATE	User signup date
country	VARCHAR(50)	User country

Sample Data

`transactions`

transaction_id	user_id	transaction_date	amount	status	store_id
101	1	2024-01-02	45.00	completed	10
102	1	2024-01-03	20.00	completed	10
103	2	2024-01-04	60.00	completed	11
104	3	2024-01-09	30.00	completed	10
105	2	2024-01-10	25.00	failed	11
106	4	2024-01-16	80.00	completed	12
107	4	2024-01-17	10.00	completed	12
108	5	2024-01-23	50.00	pending	10
109	6	2024-01-24	70.00	completed	11
110	NULL	2024-01-25	15.00	completed	11

`users`

user_id	user_name	signup_date	country
1	Ava Chen	2023-12-15	US
2	Liam Patel	2023-12-20	CA
3	Noah Kim	2024-01-05	US
4	Emma Lopez	2024-01-08	UK
5	Mia Johnson	2024-01-12	US
6	Ethan Brown	2024-01-18	CA
7	Sophia Davis	2024-01-22	UK
8	Lucas Martin	2024-01-28	NULL

Expected Output

week_start	active_purchasers	previous_week_active_purchasers	wow_growth_rate_pct
2024-01-01	2	NULL	NULL
2024-01-08	1	2	-50.00
2024-01-15	1	1	0.00
2024-01-22	1	1	0.00