SQL Joins for Retention Rates | Dataford Interview Questions

Zomato wants to measure whether customers place additional delivered orders after their first qualifying order. Write a PostgreSQL query using customers, orders, and restaurants to calculate retention by first-order month and restaurant city.

Requirements

Consider only orders with status = 'delivered' placed at active restaurants.
Assign each customer to the month and city of their first qualifying order.
Calculate retention for months 0, 1, and 2 after the cohort month, counting each customer at most once per month.
Return cohort month, city, cohort size, retained customers, and retention percentage, ordered chronologically by cohort and month offset.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique Zomato customer identifier
customer_name	VARCHAR(100)	Customer display name
signup_date	DATE	Date the customer registered

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
restaurant_id	INT	Restaurant receiving the order
order_date	DATE	Date on which the order was placed
status	VARCHAR(20)	Order lifecycle status

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique restaurant identifier
restaurant_name	VARCHAR(150)	Restaurant name shown on Zomato
city	VARCHAR(50)	Restaurant operating city
is_active	BOOLEAN	Whether the restaurant is currently active

Your question is SQL Joins for Retention Rates. Start with the requirements and the three tables 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