Get Started

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 is a part of the course

“Analyzing Business Data in SQL”

View Course

Exercise instructions

  • Select the month column from user_monthly_activity, and calculate the MoM user retention rates.
  • Join user_monthly_activity to 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;
Edit and Run Code