January Shopper Retention Rate | Dataford Interview Questions

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.

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

Your question is January Shopper Retention Rate. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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

Your question is January Shopper Retention Rate. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.