Weekly Shopper Retention Rate | Dataford Interview Questions

Your question is Weekly Shopper Retention Rate. Start with the requirements and the two tables on the right.

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 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.

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