ComenzarEmpieza gratis

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.

Este ejercicio forma parte del curso

Analyzing Business Data in SQL

Ver curso

Instrucciones del ejercicio

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

Ejercicio interactivo práctico

Prueba este ejercicio y completa el código de muestra.

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;
Editar y ejecutar código