Retention Rate SQL With Joins

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Retention Rate SQL With Joins. 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.

You need to log in / sign up to run or submit.

Problem

Tiger Analytics is preparing a customer retention dashboard. Write a PostgreSQL query that calculates how many customers active in each month also returned during the immediately following calendar month.

Requirements

Deduplicate customer activity to one row per customer per calendar month.
For each month, return the prior month's active customer count, the count retained in the next month, and the retention rate as a percentage.
Include months with no retained customers and order results chronologically.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
email	VARCHAR(150)	Customer email address

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Current order status

Tablescustomersorders

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results