Interview Guides

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

January Shopper Retention Rate

Easy

SQL & Data Manipulation

Asked at 4 companies4JoinsAggregationsDate Functions

Also asked at

Problem

Task

ShopKart wants to measure how many shoppers who signed up in January came back and placed another order later. Write a SQL query to calculate the retention rate for the January signup cohort.

Requirements

Consider only shoppers whose signup_date is in January 2024.
A shopper is retained if has_returned is TRUE.
Return the total January signups, retained January shoppers, and the retention rate as a percentage rounded to 2 decimal places.

Table Definition

shoppers

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper created an account
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

shopper_id	shopper_name	signup_date	has_returned	country
1	Ava Patel	2024-01-15	TRUE	US
2	Liam Chen	2024-01-03	FALSE	CA
3	Noah Kim	2024-02-01	TRUE	US
4	Emma Davis	2024-01-31	TRUE	UK
5	Olivia Brown	2023-12-31	TRUE	US
6	Sophia Wilson	2024-01-10	NULL	AU
7	Mason Lee	2024-01-22	FALSE	US
8	Isabella Garcia	2024-03-05	TRUE	CA

Expected Output

january_signups	retained_shoppers	retention_rate_pct
7	4	57.14

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper full name
signup_date	DATE	Date the shopper signed up
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

Example 1

Input

{"shoppers":[["1","Ava Patel","2024-01-15","true","US"],["2","Liam Chen","2024-01-03","false","CA"],["3","Noah Kim","2024-02-01","true","US"],["4","Emma Davis","2024-01-31","true","UK"],["5","Olivia Brown","2023-12-31","true","US"],["6","Sophia Wilson","2024-01-10","null","AU"],["7","Mason Lee","2024-01-22","false","US"],["8","Isabella Garcia","2024-03-05","true","CA"],["9","James Miller","2024-01-01","true","US"],["10","Mia Anderson","2024-01-20","true","null"],["11","Ethan Thomas","2024-02-14"

Output[["14","7","50.00"]]

Problem

Task

ShopKart wants to measure how many shoppers who signed up in January came back and placed another order later. Write a SQL query to calculate the retention rate for the January signup cohort.

Requirements

Consider only shoppers whose signup_date is in January 2024.
A shopper is retained if has_returned is TRUE.
Return the total January signups, retained January shoppers, and the retention rate as a percentage rounded to 2 decimal places.

Table Definition

shoppers

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper created an account
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

shopper_id	shopper_name	signup_date	has_returned	country
1	Ava Patel	2024-01-15	TRUE	US
2	Liam Chen	2024-01-03	FALSE	CA
3	Noah Kim	2024-02-01	TRUE	US
4	Emma Davis	2024-01-31	TRUE	UK
5	Olivia Brown	2023-12-31	TRUE	US
6	Sophia Wilson	2024-01-10	NULL	AU
7	Mason Lee	2024-01-22	FALSE	US
8	Isabella Garcia	2024-03-05	TRUE	CA

Expected Output

january_signups	retained_shoppers	retention_rate_pct
7	4	57.14

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper full name
signup_date	DATE	Date the shopper signed up
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

Example 1

Input

{"shoppers":[["1","Ava Patel","2024-01-15","true","US"],["2","Liam Chen","2024-01-03","false","CA"],["3","Noah Kim","2024-02-01","true","US"],["4","Emma Davis","2024-01-31","true","UK"],["5","Olivia Brown","2023-12-31","true","US"],["6","Sophia Wilson","2024-01-10","null","AU"],["7","Mason Lee","2024-01-22","false","US"],["8","Isabella Garcia","2024-03-05","true","CA"],["9","James Miller","2024-01-01","true","US"],["10","Mia Anderson","2024-01-20","true","null"],["11","Ethan Thomas","2024-02-14"

Output[["14","7","50.00"]]

Practice SQL

PostgreSQL

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

Up next

AWeekly Shopper Retention RateMedium

January Rider Retention RateMedium

Compare Channel Retention by CohortMedium

Next question

January Shopper Retention Rate

Easy

SQL & Data Manipulation

Asked at 4 companies4JoinsAggregationsDate Functions

Also asked at

Problem

Task

ShopKart wants to measure how many shoppers who signed up in January came back and placed another order later. Write a SQL query to calculate the retention rate for the January signup cohort.

Requirements

Consider only shoppers whose signup_date is in January 2024.
A shopper is retained if has_returned is TRUE.
Return the total January signups, retained January shoppers, and the retention rate as a percentage rounded to 2 decimal places.

Table Definition

shoppers

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper created an account
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

shopper_id	shopper_name	signup_date	has_returned	country
1	Ava Patel	2024-01-15	TRUE	US
2	Liam Chen	2024-01-03	FALSE	CA
3	Noah Kim	2024-02-01	TRUE	US
4	Emma Davis	2024-01-31	TRUE	UK
5	Olivia Brown	2023-12-31	TRUE	US
6	Sophia Wilson	2024-01-10	NULL	AU
7	Mason Lee	2024-01-22	FALSE	US
8	Isabella Garcia	2024-03-05	TRUE	CA

Expected Output

january_signups	retained_shoppers	retention_rate_pct
7	4	57.14

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper full name
signup_date	DATE	Date the shopper signed up
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

Example 1

Input

{"shoppers":[["1","Ava Patel","2024-01-15","true","US"],["2","Liam Chen","2024-01-03","false","CA"],["3","Noah Kim","2024-02-01","true","US"],["4","Emma Davis","2024-01-31","true","UK"],["5","Olivia Brown","2023-12-31","true","US"],["6","Sophia Wilson","2024-01-10","null","AU"],["7","Mason Lee","2024-01-22","false","US"],["8","Isabella Garcia","2024-03-05","true","CA"],["9","James Miller","2024-01-01","true","US"],["10","Mia Anderson","2024-01-20","true","null"],["11","Ethan Thomas","2024-02-14"

Output[["14","7","50.00"]]

Problem

Task

ShopKart wants to measure how many shoppers who signed up in January came back and placed another order later. Write a SQL query to calculate the retention rate for the January signup cohort.

Requirements

Consider only shoppers whose signup_date is in January 2024.
A shopper is retained if has_returned is TRUE.
Return the total January signups, retained January shoppers, and the retention rate as a percentage rounded to 2 decimal places.

Table Definition

shoppers

column	type	description
shopper_id	INT	Unique shopper ID
shopper_name	VARCHAR(100)	Shopper name
signup_date	DATE	Date the shopper created an account
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

shopper_id	shopper_name	signup_date	has_returned	country
1	Ava Patel	2024-01-15	TRUE	US
2	Liam Chen	2024-01-03	FALSE	CA
3	Noah Kim	2024-02-01	TRUE	US
4	Emma Davis	2024-01-31	TRUE	UK
5	Olivia Brown	2023-12-31	TRUE	US
6	Sophia Wilson	2024-01-10	NULL	AU
7	Mason Lee	2024-01-22	FALSE	US
8	Isabella Garcia	2024-03-05	TRUE	CA

Expected Output

january_signups	retained_shoppers	retention_rate_pct
7	4	57.14

Schema

shoppers

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper full name
signup_date	DATE	Date the shopper signed up
has_returned	BOOLEAN	Whether the shopper returned after signup
country	VARCHAR(50)	Shopper country

Sample Data

Example 1

Input

{"shoppers":[["1","Ava Patel","2024-01-15","true","US"],["2","Liam Chen","2024-01-03","false","CA"],["3","Noah Kim","2024-02-01","true","US"],["4","Emma Davis","2024-01-31","true","UK"],["5","Olivia Brown","2023-12-31","true","US"],["6","Sophia Wilson","2024-01-10","null","AU"],["7","Mason Lee","2024-01-22","false","US"],["8","Isabella Garcia","2024-03-05","true","CA"],["9","James Miller","2024-01-01","true","US"],["10","Mia Anderson","2024-01-20","true","null"],["11","Ethan Thomas","2024-02-14"

Output[["14","7","50.00"]]

Practice SQL

PostgreSQL

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

Up next

AWeekly Shopper Retention RateMedium

January Rider Retention RateMedium

Compare Channel Retention by CohortMedium

Next question