Retention rate
Bob's requested your help again now that you're done with Carol's MAU monitor. His meeting with potential investors is fast approaching, and he wants to wrap up his pitch deck. You've already helped him with the registrations running total by month and MAU line charts; the investors, Bob says, would be convinced that Delivr is growing both in new users and in MAUs.
However, Bob wants to show that Delivr not only attracts new users but also retains existing users. Send him a table of MoM retention rates so that he can highlight Delivr's high user loyalty.
This exercise is part of the course
Analyzing Business Data in SQL
Exercise instructions
- Select the month column from
user_monthly_activity, and calculate the MoM user retention rates. - Join
user_monthly_activityto itself on the user ID and the month, pushed forward one month.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH user_monthly_activity AS (
SELECT DISTINCT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
user_id
FROM orders)
SELECT
-- Calculate the MoM retention rates
previous.delivr_month,
ROUND(
___ /
___,
2) AS retention_rate
FROM user_monthly_activity AS previous
LEFT JOIN user_monthly_activity AS current
-- Fill in the user and month join conditions
ON ___
AND ___
GROUP BY previous.delivr_month
ORDER BY previous.delivr_month ASC;