Interview Guides

Weekly Shopper Retention Rate | Dataford Interview Questions - Dataford - Ace your Interview

Weekly Shopper Retention Rate

Medium

SQL & Data Manipulation

Asked at 2 companies2AggregationsWindow FunctionsDate Functions

Also asked at

Problem

Task

ShopKart wants to measure how many shoppers return in the following week after making a purchase. Write a SQL query to calculate week-over-week shopper retention.

A shopper is considered retained in week W if they made at least one completed order in week W and also made at least one completed order in week W + 1.

Requirements

Use only completed orders.
Aggregate activity by shopper and calendar week using DATE_TRUNC('week', order_date).
For each week, return:
- the week start date
- the number of active shoppers in that week
- the number of those shoppers who returned the next week
- the retention rate as retained_shoppers / active_shoppers, rounded to 4 decimals
Exclude the final week in the dataset if there is no following week to evaluate retention against.
Order results by week start date ascending.

Tables

`shoppers`

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper registered
region	VARCHAR(50)	Shopper region

`orders`

column	type	description
order_id	INT	Unique order ID
shopper_id	INT	Shopper who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status
order_total	NUMERIC(10,2)	Order amount

Sample data

`shoppers`

shopper_id	shopper_name	signup_date	region
1	Ava Patel	2024-12-20	East
2	Liam Chen	2024-12-22	West
3	Noah Kim	2024-12-28	South
4	Emma Diaz	2025-01-02	North

`orders`

order_id	shopper_id	order_date	order_status	order_total
101	1	2025-01-06	completed	45.00
102	1	2025-01-13	completed	30.00
103	2	2025-01-07	completed	25.00
104	3	2025-01-14	cancelled	60.00
105	4	2025-01-20	completed	80.00

Expected output

week_start	active_shoppers	retained_shoppers	retention_rate
2025-01-06	3	2	0.6667
2025-01-13	3	2	0.6667
2025-01-20	3	1	0.3333

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Full name of the shopper
signup_date	DATE	Date the shopper signed up
region	VARCHAR(50)	Shopper region

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_total	NUMERIC(10,2)	Total order amount

Sample Data

Example 1

Input

{"orders":[["101","1","2025-01-13","completed","30.00"],["102","1","2025-01-06","completed","45.00"],["103","2","2025-01-07","completed","25.00"],["104","2","2025-01-14","completed","55.00"],["105","3","2025-01-08","completed","40.00"],["106","3","2025-01-15","cancelled","60.00"],["107","4","2025-01-14","completed","80.00"],["108","4","2025-01-20","completed","35.00"],["109","5","2025-01-16","completed","20.00"],["110","5","2025-01-21","completed","22.00"],["111","6","2025-01-21","completed","50

Output[["2025-01-06","4","2","0.5000"],["2025-01-13","5","3","0.6000"],["2025-01-20","4","1","0.2500"]]

Problem

Task

ShopKart wants to measure how many shoppers return in the following week after making a purchase. Write a SQL query to calculate week-over-week shopper retention.

A shopper is considered retained in week W if they made at least one completed order in week W and also made at least one completed order in week W + 1.

Requirements

Use only completed orders.
Aggregate activity by shopper and calendar week using DATE_TRUNC('week', order_date).
For each week, return:
- the week start date
- the number of active shoppers in that week
- the number of those shoppers who returned the next week
- the retention rate as retained_shoppers / active_shoppers, rounded to 4 decimals
Exclude the final week in the dataset if there is no following week to evaluate retention against.
Order results by week start date ascending.

Tables

`shoppers`

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper registered
region	VARCHAR(50)	Shopper region

`orders`

column	type	description
order_id	INT	Unique order ID
shopper_id	INT	Shopper who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status
order_total	NUMERIC(10,2)	Order amount

Sample data

`shoppers`

shopper_id	shopper_name	signup_date	region
1	Ava Patel	2024-12-20	East
2	Liam Chen	2024-12-22	West
3	Noah Kim	2024-12-28	South
4	Emma Diaz	2025-01-02	North

`orders`

order_id	shopper_id	order_date	order_status	order_total
101	1	2025-01-06	completed	45.00
102	1	2025-01-13	completed	30.00
103	2	2025-01-07	completed	25.00
104	3	2025-01-14	cancelled	60.00
105	4	2025-01-20	completed	80.00

Expected output

week_start	active_shoppers	retained_shoppers	retention_rate
2025-01-06	3	2	0.6667
2025-01-13	3	2	0.6667
2025-01-20	3	1	0.3333

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Full name of the shopper
signup_date	DATE	Date the shopper signed up
region	VARCHAR(50)	Shopper region

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_total	NUMERIC(10,2)	Total order amount

Sample Data

Example 1

Input

{"orders":[["101","1","2025-01-13","completed","30.00"],["102","1","2025-01-06","completed","45.00"],["103","2","2025-01-07","completed","25.00"],["104","2","2025-01-14","completed","55.00"],["105","3","2025-01-08","completed","40.00"],["106","3","2025-01-15","cancelled","60.00"],["107","4","2025-01-14","completed","80.00"],["108","4","2025-01-20","completed","35.00"],["109","5","2025-01-16","completed","20.00"],["110","5","2025-01-21","completed","22.00"],["111","6","2025-01-21","completed","50

Output[["2025-01-06","4","2","0.5000"],["2025-01-13","5","3","0.6000"],["2025-01-20","4","1","0.2500"]]

Practice SQL

PostgreSQL

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

Up next

IJanuary Shopper Retention RateEasy

Weekly Active Purchaser Growth RateMedium

Weekly Signup Cohort RetentionMedium

Next question

Weekly Shopper Retention Rate

Medium

SQL & Data Manipulation

Asked at 2 companies2AggregationsWindow FunctionsDate Functions

Also asked at

Problem

Task

ShopKart wants to measure how many shoppers return in the following week after making a purchase. Write a SQL query to calculate week-over-week shopper retention.

A shopper is considered retained in week W if they made at least one completed order in week W and also made at least one completed order in week W + 1.

Requirements

Use only completed orders.
Aggregate activity by shopper and calendar week using DATE_TRUNC('week', order_date).
For each week, return:
- the week start date
- the number of active shoppers in that week
- the number of those shoppers who returned the next week
- the retention rate as retained_shoppers / active_shoppers, rounded to 4 decimals
Exclude the final week in the dataset if there is no following week to evaluate retention against.
Order results by week start date ascending.

Tables

`shoppers`

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper registered
region	VARCHAR(50)	Shopper region

`orders`

column	type	description
order_id	INT	Unique order ID
shopper_id	INT	Shopper who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status
order_total	NUMERIC(10,2)	Order amount

Sample data

`shoppers`

shopper_id	shopper_name	signup_date	region
1	Ava Patel	2024-12-20	East
2	Liam Chen	2024-12-22	West
3	Noah Kim	2024-12-28	South
4	Emma Diaz	2025-01-02	North

`orders`

order_id	shopper_id	order_date	order_status	order_total
101	1	2025-01-06	completed	45.00
102	1	2025-01-13	completed	30.00
103	2	2025-01-07	completed	25.00
104	3	2025-01-14	cancelled	60.00
105	4	2025-01-20	completed	80.00

Expected output

week_start	active_shoppers	retained_shoppers	retention_rate
2025-01-06	3	2	0.6667
2025-01-13	3	2	0.6667
2025-01-20	3	1	0.3333

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Full name of the shopper
signup_date	DATE	Date the shopper signed up
region	VARCHAR(50)	Shopper region

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_total	NUMERIC(10,2)	Total order amount

Sample Data

Example 1

Input

{"orders":[["101","1","2025-01-13","completed","30.00"],["102","1","2025-01-06","completed","45.00"],["103","2","2025-01-07","completed","25.00"],["104","2","2025-01-14","completed","55.00"],["105","3","2025-01-08","completed","40.00"],["106","3","2025-01-15","cancelled","60.00"],["107","4","2025-01-14","completed","80.00"],["108","4","2025-01-20","completed","35.00"],["109","5","2025-01-16","completed","20.00"],["110","5","2025-01-21","completed","22.00"],["111","6","2025-01-21","completed","50

Output[["2025-01-06","4","2","0.5000"],["2025-01-13","5","3","0.6000"],["2025-01-20","4","1","0.2500"]]

Problem

Task

ShopKart wants to measure how many shoppers return in the following week after making a purchase. Write a SQL query to calculate week-over-week shopper retention.

A shopper is considered retained in week W if they made at least one completed order in week W and also made at least one completed order in week W + 1.

Requirements

Use only completed orders.
Aggregate activity by shopper and calendar week using DATE_TRUNC('week', order_date).
For each week, return:
- the week start date
- the number of active shoppers in that week
- the number of those shoppers who returned the next week
- the retention rate as retained_shoppers / active_shoppers, rounded to 4 decimals
Exclude the final week in the dataset if there is no following week to evaluate retention against.
Order results by week start date ascending.

Tables

`shoppers`

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper registered
region	VARCHAR(50)	Shopper region

`orders`

column	type	description
order_id	INT	Unique order ID
shopper_id	INT	Shopper who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status
order_total	NUMERIC(10,2)	Order amount

Sample data

`shoppers`

shopper_id	shopper_name	signup_date	region
1	Ava Patel	2024-12-20	East
2	Liam Chen	2024-12-22	West
3	Noah Kim	2024-12-28	South
4	Emma Diaz	2025-01-02	North

`orders`

order_id	shopper_id	order_date	order_status	order_total
101	1	2025-01-06	completed	45.00
102	1	2025-01-13	completed	30.00
103	2	2025-01-07	completed	25.00
104	3	2025-01-14	cancelled	60.00
105	4	2025-01-20	completed	80.00

Expected output

week_start	active_shoppers	retained_shoppers	retention_rate
2025-01-06	3	2	0.6667
2025-01-13	3	2	0.6667
2025-01-20	3	1	0.3333

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Full name of the shopper
signup_date	DATE	Date the shopper signed up
region	VARCHAR(50)	Shopper region

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
shopper_id	INT	Shopper who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_total	NUMERIC(10,2)	Total order amount

Sample Data

Example 1

Input

{"orders":[["101","1","2025-01-13","completed","30.00"],["102","1","2025-01-06","completed","45.00"],["103","2","2025-01-07","completed","25.00"],["104","2","2025-01-14","completed","55.00"],["105","3","2025-01-08","completed","40.00"],["106","3","2025-01-15","cancelled","60.00"],["107","4","2025-01-14","completed","80.00"],["108","4","2025-01-20","completed","35.00"],["109","5","2025-01-16","completed","20.00"],["110","5","2025-01-21","completed","22.00"],["111","6","2025-01-21","completed","50

Output[["2025-01-06","4","2","0.5000"],["2025-01-13","5","3","0.6000"],["2025-01-20","4","1","0.2500"]]

Practice SQL

PostgreSQL

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

Up next

IJanuary Shopper Retention RateEasy

Weekly Active Purchaser Growth RateMedium

Weekly Signup Cohort RetentionMedium

Next question