Interview Guides

Repeat Purchase Analysis Across Meal Kits | Dataford Interview Questions - Dataford - Ace your Interview

Repeat Purchase Analysis Across Meal Kits

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsCTEs

Also asked at

Problem

Task

You are given the task of analyzing repeat purchase behavior for HelloFresh meal kits by joining users, orders, and products. Write a PostgreSQL query that returns one row per product showing how many distinct users bought that product at least once, how many of those users placed more than one delivered order containing the same product, and the repeat purchase rate as a percentage. Only include delivered orders, and keep products even if they have never appeared in a delivered order.

Schema

`users`

column	type	description
user_id	INT	Unique user identifier
full_name	VARCHAR(100)	User name
signup_channel	VARCHAR(50)	Acquisition channel
country_code	VARCHAR(2)	User country

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order lifecycle status
product_id	INT	Ordered product

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

Sample data

`orders`

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
104	2	2024-02-05	delivered	102
108	4	2024-03-01	cancelled	104
111	7	2024-03-15	delivered	NULL

Expected output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
101	Chicken Teriyaki	2	1	50.00
102	Veggie Burrito Bowl	2	1	50.00
103	Creamy Mushroom Pasta	2	1	50.00
104	Salmon Rice Bowl	1	0	0.00
105	Beef Tacos	0	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
full_name	VARCHAR(100)	User full name
signup_channel	VARCHAR(50)	Marketing channel used to acquire the user
country_code	VARCHAR(2)	User country code

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order status such as delivered, cancelled, or pending
product_id	INT	Product included in the order

products

Column	Type	Description
product_idPK	INT	Primary key for each product
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

users

user_id	full_name	signup_channel	country_code
3	Maya Patel	referral	US
1	Ava Chen	paid_social	US
6	Noah Kim		US
2	Liam Garcia	organic_search	CA
8	Leo Brown	paid_social
5	Emma Wilson	affiliate	GB
4	Sophia Davis	organic_search	US
7	Olivia Martin	referral	CA
1	Daniel Perez	friend_invite	PT
2	Isabella Moore	instagram	SK
3	Maya Patel	instagram	CZ
4	Leo Brown	null	null
5	Evelyn Nelson	affiliate	CA
6	Olivia Martin	app_store	FR
7	Jack Campbell	out_of_home	AU
8	Noah Kim	tv	CZ
9	Charlotte Young	retargeting	ES
10	Charlotte Young	tv	null
11	Grace Parker	google_ads	SK
12	Noah Kim	podcast	NZ
13	Abigail Mitchell	instagram	IT
14	Isabella Moore	podcast	null
15	Scarlett Phillips	direct	DE
16	Grace Parker	coupon_site	LU
17	Ava Chen	retargeting	FR
18	Mia Thompson	display	ES
19	Leo Brown	retargeting	LU
20	Olivia Martin	partnership	NZ
21	Benjamin Allen	paid_social	PL

orders

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
109	5	2024-03-10	delivered	103
104	2	2024-02-05	delivered	102
111	7	2024-03-15	delivered
101	1	2024-01-05	delivered	101
110	6	2024-03-12	pending	101
107	4	2024-02-20	delivered	104
103	1	2024-01-25	cancelled	102
108	4	2024-03-01	cancelled	104
106	3	2024-02-15	delivered	103
105	2	2024-02-12	delivered	102
112		2024-03-18	delivered	102
1	44	2024-02-06	scheduled	74
2	24	2024-03-15	cancelled	67
3	72	2024-02-15	address_issue	22
4	69	2024-02-16	on_hold	85
5	82	2024-01-07	customer_cancelled	88
6	15	2024-01-29	failed	14
7	57	2024-02-21	delivered	1
8	11	2024-02-12	scheduled	91
9	99	2024-01-31	failed	90
10	98	2024-02-19	awaiting_payment	19
11	56	2024-02-13	processing	13
12	16	2024-02-13	customer_cancelled	69
13	66	2024-01-19	payment_review	37
14	99	2024-02-29	subscription_paused	78

products

product_id	product_name	category
103	Creamy Mushroom Pasta	Vegetarian
101	Chicken Teriyaki	Meat
105	Beef Tacos	Meat
102	Veggie Burrito Bowl	Vegetarian
106
104	Salmon Rice Bowl	Seafood
107	Falafel Couscous	Vegetarian
108	Pork Dan Dan Noodles	Meat
1	Creamy Tuscan Chicken	Burger
2	Beef Tacos	Pasta
3	Creamy Mushroom Pasta	Mexican
4	Paneer Tikka Masala	High Fiber
5	Sausage Rigatoni	Curry
6	Maple Mustard Pork	Seafood
7	BBQ Chicken Flatbread	Pasta
8	Beef Tacos	Noodles
9	Sweet Chili Turkey Bowls	Protein Rich
10	Smoky Black Bean Chili	Pasta
11		null
12	Sausage Rigatoni	Comfort Food
13	Thai Basil Beef	Protein Rich
14	Steakhouse Burger	null
15	Mediterranean Chickpea Salad	Seasonal
16	Maple Mustard Pork	Chef Special
17	Herby Lemon Chicken	Asian
18	Sweet Chili Turkey Bowls	High Fiber
19	Sweet Chili Turkey Bowls	Comfort Food

Expected Output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
2	Beef Tacos	0	0	null
3	Creamy Mushroom Pasta	0	0	null
4	Paneer Tikka Masala	0	0	null
5	Sausage Rigatoni	0	0	null
6	Maple Mustard Pork	0	0	null
7	BBQ Chicken Flatbread	0	0	null
8	Beef Tacos	0	0	null
9	Sweet Chili Turkey Bowls	0	0	null
10	Smoky Black Bean Chili	0	0	null
11	null	0	0	null
12	Sausage Rigatoni	0	0	null
13	Thai Basil Beef	0	0	null
14	Steakhouse Burger	0	0	null
15	Mediterranean Chickpea Salad	0	0	null
16	Maple Mustard Pork	0	0	null
17	Herby Lemon Chicken	0	0	null
18	Sweet Chili Turkey Bowls	0	0	null
19	Sweet Chili Turkey Bowls	0	0	null
105	Beef Tacos	0	0	null
106	null	0	0	null
107	Falafel Couscous	0	0	null
108	Pork Dan Dan Noodles	0	0	null
101	Chicken Teriyaki	1	1	100.00
102	Veggie Burrito Bowl	1	1	100.00
103	Creamy Mushroom Pasta	2	0	0.00
1	Creamy Tuscan Chicken	1	0	0.00
104	Salmon Rice Bowl	1	0	0.00

Problem

Task

Schema

`users`

column	type	description
user_id	INT	Unique user identifier
full_name	VARCHAR(100)	User name
signup_channel	VARCHAR(50)	Acquisition channel
country_code	VARCHAR(2)	User country

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order lifecycle status
product_id	INT	Ordered product

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

Sample data

`orders`

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
104	2	2024-02-05	delivered	102
108	4	2024-03-01	cancelled	104
111	7	2024-03-15	delivered	NULL

Expected output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
101	Chicken Teriyaki	2	1	50.00
102	Veggie Burrito Bowl	2	1	50.00
103	Creamy Mushroom Pasta	2	1	50.00
104	Salmon Rice Bowl	1	0	0.00
105	Beef Tacos	0	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
full_name	VARCHAR(100)	User full name
signup_channel	VARCHAR(50)	Marketing channel used to acquire the user
country_code	VARCHAR(2)	User country code

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order status such as delivered, cancelled, or pending
product_id	INT	Product included in the order

products

Column	Type	Description
product_idPK	INT	Primary key for each product
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

users

user_id	full_name	signup_channel	country_code
3	Maya Patel	referral	US
1	Ava Chen	paid_social	US
6	Noah Kim		US
2	Liam Garcia	organic_search	CA
8	Leo Brown	paid_social
5	Emma Wilson	affiliate	GB
4	Sophia Davis	organic_search	US
7	Olivia Martin	referral	CA
1	Daniel Perez	friend_invite	PT
2	Isabella Moore	instagram	SK
3	Maya Patel	instagram	CZ
4	Leo Brown	null	null
5	Evelyn Nelson	affiliate	CA
6	Olivia Martin	app_store	FR
7	Jack Campbell	out_of_home	AU
8	Noah Kim	tv	CZ
9	Charlotte Young	retargeting	ES
10	Charlotte Young	tv	null
11	Grace Parker	google_ads	SK
12	Noah Kim	podcast	NZ
13	Abigail Mitchell	instagram	IT
14	Isabella Moore	podcast	null
15	Scarlett Phillips	direct	DE
16	Grace Parker	coupon_site	LU
17	Ava Chen	retargeting	FR
18	Mia Thompson	display	ES
19	Leo Brown	retargeting	LU
20	Olivia Martin	partnership	NZ
21	Benjamin Allen	paid_social	PL

orders

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
109	5	2024-03-10	delivered	103
104	2	2024-02-05	delivered	102
111	7	2024-03-15	delivered
101	1	2024-01-05	delivered	101
110	6	2024-03-12	pending	101
107	4	2024-02-20	delivered	104
103	1	2024-01-25	cancelled	102
108	4	2024-03-01	cancelled	104
106	3	2024-02-15	delivered	103
105	2	2024-02-12	delivered	102
112		2024-03-18	delivered	102
1	44	2024-02-06	scheduled	74
2	24	2024-03-15	cancelled	67
3	72	2024-02-15	address_issue	22
4	69	2024-02-16	on_hold	85
5	82	2024-01-07	customer_cancelled	88
6	15	2024-01-29	failed	14
7	57	2024-02-21	delivered	1
8	11	2024-02-12	scheduled	91
9	99	2024-01-31	failed	90
10	98	2024-02-19	awaiting_payment	19
11	56	2024-02-13	processing	13
12	16	2024-02-13	customer_cancelled	69
13	66	2024-01-19	payment_review	37
14	99	2024-02-29	subscription_paused	78

products

product_id	product_name	category
103	Creamy Mushroom Pasta	Vegetarian
101	Chicken Teriyaki	Meat
105	Beef Tacos	Meat
102	Veggie Burrito Bowl	Vegetarian
106
104	Salmon Rice Bowl	Seafood
107	Falafel Couscous	Vegetarian
108	Pork Dan Dan Noodles	Meat
1	Creamy Tuscan Chicken	Burger
2	Beef Tacos	Pasta
3	Creamy Mushroom Pasta	Mexican
4	Paneer Tikka Masala	High Fiber
5	Sausage Rigatoni	Curry
6	Maple Mustard Pork	Seafood
7	BBQ Chicken Flatbread	Pasta
8	Beef Tacos	Noodles
9	Sweet Chili Turkey Bowls	Protein Rich
10	Smoky Black Bean Chili	Pasta
11		null
12	Sausage Rigatoni	Comfort Food
13	Thai Basil Beef	Protein Rich
14	Steakhouse Burger	null
15	Mediterranean Chickpea Salad	Seasonal
16	Maple Mustard Pork	Chef Special
17	Herby Lemon Chicken	Asian
18	Sweet Chili Turkey Bowls	High Fiber
19	Sweet Chili Turkey Bowls	Comfort Food

Expected Output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
2	Beef Tacos	0	0	null
3	Creamy Mushroom Pasta	0	0	null
4	Paneer Tikka Masala	0	0	null
5	Sausage Rigatoni	0	0	null
6	Maple Mustard Pork	0	0	null
7	BBQ Chicken Flatbread	0	0	null
8	Beef Tacos	0	0	null
9	Sweet Chili Turkey Bowls	0	0	null
10	Smoky Black Bean Chili	0	0	null
11	null	0	0	null
12	Sausage Rigatoni	0	0	null
13	Thai Basil Beef	0	0	null
14	Steakhouse Burger	0	0	null
15	Mediterranean Chickpea Salad	0	0	null
16	Maple Mustard Pork	0	0	null
17	Herby Lemon Chicken	0	0	null
18	Sweet Chili Turkey Bowls	0	0	null
19	Sweet Chili Turkey Bowls	0	0	null
105	Beef Tacos	0	0	null
106	null	0	0	null
107	Falafel Couscous	0	0	null
108	Pork Dan Dan Noodles	0	0	null
101	Chicken Teriyaki	1	1	100.00
102	Veggie Burrito Bowl	1	1	100.00
103	Creamy Mushroom Pasta	2	0	0.00
1	Creamy Tuscan Chicken	1	0	0.00
104	Salmon Rice Bowl	1	0	0.00

Practice SQL

PostgreSQL

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

Up next

Channel Conversion Rate CalculationEasy

Define HelloFresh North Star MetricEasy

Same-Day Riders and EatersMedium

Next question

Repeat Purchase Analysis Across Meal Kits

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsCTEs

Also asked at

Problem

Task

Schema

`users`

column	type	description
user_id	INT	Unique user identifier
full_name	VARCHAR(100)	User name
signup_channel	VARCHAR(50)	Acquisition channel
country_code	VARCHAR(2)	User country

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order lifecycle status
product_id	INT	Ordered product

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

Sample data

`orders`

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
104	2	2024-02-05	delivered	102
108	4	2024-03-01	cancelled	104
111	7	2024-03-15	delivered	NULL

Expected output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
101	Chicken Teriyaki	2	1	50.00
102	Veggie Burrito Bowl	2	1	50.00
103	Creamy Mushroom Pasta	2	1	50.00
104	Salmon Rice Bowl	1	0	0.00
105	Beef Tacos	0	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
full_name	VARCHAR(100)	User full name
signup_channel	VARCHAR(50)	Marketing channel used to acquire the user
country_code	VARCHAR(2)	User country code

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order status such as delivered, cancelled, or pending
product_id	INT	Product included in the order

products

Column	Type	Description
product_idPK	INT	Primary key for each product
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

users

user_id	full_name	signup_channel	country_code
3	Maya Patel	referral	US
1	Ava Chen	paid_social	US
6	Noah Kim		US
2	Liam Garcia	organic_search	CA
8	Leo Brown	paid_social
5	Emma Wilson	affiliate	GB
4	Sophia Davis	organic_search	US
7	Olivia Martin	referral	CA
1	Daniel Perez	friend_invite	PT
2	Isabella Moore	instagram	SK
3	Maya Patel	instagram	CZ
4	Leo Brown	null	null
5	Evelyn Nelson	affiliate	CA
6	Olivia Martin	app_store	FR
7	Jack Campbell	out_of_home	AU
8	Noah Kim	tv	CZ
9	Charlotte Young	retargeting	ES
10	Charlotte Young	tv	null
11	Grace Parker	google_ads	SK
12	Noah Kim	podcast	NZ
13	Abigail Mitchell	instagram	IT
14	Isabella Moore	podcast	null
15	Scarlett Phillips	direct	DE
16	Grace Parker	coupon_site	LU
17	Ava Chen	retargeting	FR
18	Mia Thompson	display	ES
19	Leo Brown	retargeting	LU
20	Olivia Martin	partnership	NZ
21	Benjamin Allen	paid_social	PL

orders

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
109	5	2024-03-10	delivered	103
104	2	2024-02-05	delivered	102
111	7	2024-03-15	delivered
101	1	2024-01-05	delivered	101
110	6	2024-03-12	pending	101
107	4	2024-02-20	delivered	104
103	1	2024-01-25	cancelled	102
108	4	2024-03-01	cancelled	104
106	3	2024-02-15	delivered	103
105	2	2024-02-12	delivered	102
112		2024-03-18	delivered	102
1	44	2024-02-06	scheduled	74
2	24	2024-03-15	cancelled	67
3	72	2024-02-15	address_issue	22
4	69	2024-02-16	on_hold	85
5	82	2024-01-07	customer_cancelled	88
6	15	2024-01-29	failed	14
7	57	2024-02-21	delivered	1
8	11	2024-02-12	scheduled	91
9	99	2024-01-31	failed	90
10	98	2024-02-19	awaiting_payment	19
11	56	2024-02-13	processing	13
12	16	2024-02-13	customer_cancelled	69
13	66	2024-01-19	payment_review	37
14	99	2024-02-29	subscription_paused	78

products

product_id	product_name	category
103	Creamy Mushroom Pasta	Vegetarian
101	Chicken Teriyaki	Meat
105	Beef Tacos	Meat
102	Veggie Burrito Bowl	Vegetarian
106
104	Salmon Rice Bowl	Seafood
107	Falafel Couscous	Vegetarian
108	Pork Dan Dan Noodles	Meat
1	Creamy Tuscan Chicken	Burger
2	Beef Tacos	Pasta
3	Creamy Mushroom Pasta	Mexican
4	Paneer Tikka Masala	High Fiber
5	Sausage Rigatoni	Curry
6	Maple Mustard Pork	Seafood
7	BBQ Chicken Flatbread	Pasta
8	Beef Tacos	Noodles
9	Sweet Chili Turkey Bowls	Protein Rich
10	Smoky Black Bean Chili	Pasta
11		null
12	Sausage Rigatoni	Comfort Food
13	Thai Basil Beef	Protein Rich
14	Steakhouse Burger	null
15	Mediterranean Chickpea Salad	Seasonal
16	Maple Mustard Pork	Chef Special
17	Herby Lemon Chicken	Asian
18	Sweet Chili Turkey Bowls	High Fiber
19	Sweet Chili Turkey Bowls	Comfort Food

Expected Output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
2	Beef Tacos	0	0	null
3	Creamy Mushroom Pasta	0	0	null
4	Paneer Tikka Masala	0	0	null
5	Sausage Rigatoni	0	0	null
6	Maple Mustard Pork	0	0	null
7	BBQ Chicken Flatbread	0	0	null
8	Beef Tacos	0	0	null
9	Sweet Chili Turkey Bowls	0	0	null
10	Smoky Black Bean Chili	0	0	null
11	null	0	0	null
12	Sausage Rigatoni	0	0	null
13	Thai Basil Beef	0	0	null
14	Steakhouse Burger	0	0	null
15	Mediterranean Chickpea Salad	0	0	null
16	Maple Mustard Pork	0	0	null
17	Herby Lemon Chicken	0	0	null
18	Sweet Chili Turkey Bowls	0	0	null
19	Sweet Chili Turkey Bowls	0	0	null
105	Beef Tacos	0	0	null
106	null	0	0	null
107	Falafel Couscous	0	0	null
108	Pork Dan Dan Noodles	0	0	null
101	Chicken Teriyaki	1	1	100.00
102	Veggie Burrito Bowl	1	1	100.00
103	Creamy Mushroom Pasta	2	0	0.00
1	Creamy Tuscan Chicken	1	0	0.00
104	Salmon Rice Bowl	1	0	0.00

Problem

Task

Schema

`users`

column	type	description
user_id	INT	Unique user identifier
full_name	VARCHAR(100)	User name
signup_channel	VARCHAR(50)	Acquisition channel
country_code	VARCHAR(2)	User country

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order lifecycle status
product_id	INT	Ordered product

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

Sample data

`orders`

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
104	2	2024-02-05	delivered	102
108	4	2024-03-01	cancelled	104
111	7	2024-03-15	delivered	NULL

Expected output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
101	Chicken Teriyaki	2	1	50.00
102	Veggie Burrito Bowl	2	1	50.00
103	Creamy Mushroom Pasta	2	1	50.00
104	Salmon Rice Bowl	1	0	0.00
105	Beef Tacos	0	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
full_name	VARCHAR(100)	User full name
signup_channel	VARCHAR(50)	Marketing channel used to acquire the user
country_code	VARCHAR(2)	User country code

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order status such as delivered, cancelled, or pending
product_id	INT	Product included in the order

products

Column	Type	Description
product_idPK	INT	Primary key for each product
product_name	VARCHAR(100)	HelloFresh product name
category	VARCHAR(50)	Product category

users

user_id	full_name	signup_channel	country_code
3	Maya Patel	referral	US
1	Ava Chen	paid_social	US
6	Noah Kim		US
2	Liam Garcia	organic_search	CA
8	Leo Brown	paid_social
5	Emma Wilson	affiliate	GB
4	Sophia Davis	organic_search	US
7	Olivia Martin	referral	CA
1	Daniel Perez	friend_invite	PT
2	Isabella Moore	instagram	SK
3	Maya Patel	instagram	CZ
4	Leo Brown	null	null
5	Evelyn Nelson	affiliate	CA
6	Olivia Martin	app_store	FR
7	Jack Campbell	out_of_home	AU
8	Noah Kim	tv	CZ
9	Charlotte Young	retargeting	ES
10	Charlotte Young	tv	null
11	Grace Parker	google_ads	SK
12	Noah Kim	podcast	NZ
13	Abigail Mitchell	instagram	IT
14	Isabella Moore	podcast	null
15	Scarlett Phillips	direct	DE
16	Grace Parker	coupon_site	LU
17	Ava Chen	retargeting	FR
18	Mia Thompson	display	ES
19	Leo Brown	retargeting	LU
20	Olivia Martin	partnership	NZ
21	Benjamin Allen	paid_social	PL

orders

order_id	user_id	order_date	order_status	product_id
102	1	2024-01-20	delivered	101
109	5	2024-03-10	delivered	103
104	2	2024-02-05	delivered	102
111	7	2024-03-15	delivered
101	1	2024-01-05	delivered	101
110	6	2024-03-12	pending	101
107	4	2024-02-20	delivered	104
103	1	2024-01-25	cancelled	102
108	4	2024-03-01	cancelled	104
106	3	2024-02-15	delivered	103
105	2	2024-02-12	delivered	102
112		2024-03-18	delivered	102
1	44	2024-02-06	scheduled	74
2	24	2024-03-15	cancelled	67
3	72	2024-02-15	address_issue	22
4	69	2024-02-16	on_hold	85
5	82	2024-01-07	customer_cancelled	88
6	15	2024-01-29	failed	14
7	57	2024-02-21	delivered	1
8	11	2024-02-12	scheduled	91
9	99	2024-01-31	failed	90
10	98	2024-02-19	awaiting_payment	19
11	56	2024-02-13	processing	13
12	16	2024-02-13	customer_cancelled	69
13	66	2024-01-19	payment_review	37
14	99	2024-02-29	subscription_paused	78

products

product_id	product_name	category
103	Creamy Mushroom Pasta	Vegetarian
101	Chicken Teriyaki	Meat
105	Beef Tacos	Meat
102	Veggie Burrito Bowl	Vegetarian
106
104	Salmon Rice Bowl	Seafood
107	Falafel Couscous	Vegetarian
108	Pork Dan Dan Noodles	Meat
1	Creamy Tuscan Chicken	Burger
2	Beef Tacos	Pasta
3	Creamy Mushroom Pasta	Mexican
4	Paneer Tikka Masala	High Fiber
5	Sausage Rigatoni	Curry
6	Maple Mustard Pork	Seafood
7	BBQ Chicken Flatbread	Pasta
8	Beef Tacos	Noodles
9	Sweet Chili Turkey Bowls	Protein Rich
10	Smoky Black Bean Chili	Pasta
11		null
12	Sausage Rigatoni	Comfort Food
13	Thai Basil Beef	Protein Rich
14	Steakhouse Burger	null
15	Mediterranean Chickpea Salad	Seasonal
16	Maple Mustard Pork	Chef Special
17	Herby Lemon Chicken	Asian
18	Sweet Chili Turkey Bowls	High Fiber
19	Sweet Chili Turkey Bowls	Comfort Food

Expected Output

product_id	product_name	unique_buyers	repeat_buyers	repeat_purchase_rate
2	Beef Tacos	0	0	null
3	Creamy Mushroom Pasta	0	0	null
4	Paneer Tikka Masala	0	0	null
5	Sausage Rigatoni	0	0	null
6	Maple Mustard Pork	0	0	null
7	BBQ Chicken Flatbread	0	0	null
8	Beef Tacos	0	0	null
9	Sweet Chili Turkey Bowls	0	0	null
10	Smoky Black Bean Chili	0	0	null
11	null	0	0	null
12	Sausage Rigatoni	0	0	null
13	Thai Basil Beef	0	0	null
14	Steakhouse Burger	0	0	null
15	Mediterranean Chickpea Salad	0	0	null
16	Maple Mustard Pork	0	0	null
17	Herby Lemon Chicken	0	0	null
18	Sweet Chili Turkey Bowls	0	0	null
19	Sweet Chili Turkey Bowls	0	0	null
105	Beef Tacos	0	0	null
106	null	0	0	null
107	Falafel Couscous	0	0	null
108	Pork Dan Dan Noodles	0	0	null
101	Chicken Teriyaki	1	1	100.00
102	Veggie Burrito Bowl	1	1	100.00
103	Creamy Mushroom Pasta	2	0	0.00
1	Creamy Tuscan Chicken	1	0	0.00
104	Salmon Rice Bowl	1	0	0.00

Practice SQL

PostgreSQL

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

Up next

Channel Conversion Rate CalculationEasy

Define HelloFresh North Star MetricEasy

Same-Day Riders and EatersMedium

Next question