**# month-on-month = Variation on a monthly basis.**

SELECT
      Activity_year,
      Activity_Month,
      Activity_Month_number,
	    Active_users,
      LAG(Active_users,1) OVER(
      -- LAG provides access to a row at a given physical offset that comes before 
      -- the current row. Use this analytic function in a SELECT statement to 
      -- compare values in the current row with values in a previous row.
      -- 1 is the number to shift one row down
							                   ORDER BY Activity_year, Activity_Month_number
                               ) AS Last_month
FROM monthly_active_users;

WITH cte_view as (
SELECT
      Activity_year,
      Activity_Month,
      Activity_Month_number,
	    Active_users,
      LAG(Active_users,1) OVER(
							                   ORDER BY Activity_year, Activity_Month_number
                               ) AS Last_month
FROM monthly_active_users)

SELECT
	  *,
      (Active_users - Last_Month) / Active_users * 100 as diff
      -- here we calculate the percentage of the month-on-month variation
FROM cte_view;

**# Customer Retention or Churn. Rate.**

-- Customer Retention = Customer retention rate is the percentage of existing customers 
--                      who remain customers after a given period.

CREATE OR REPLACE VIEW retained_customers AS
SELECT
	  d2.Activity_Month,
	  COUNT(DISTINCT d1.account_id) as retained_customers
FROM distinct_users d1
JOIN bank.distinct_users d2 ON d1.account_id = d2.account_id 
          AND d2.Activity_Month = DATE_ADD(d1.Activity_Month, INTERVAL 1 MONTH)
GROUP BY 1
ORDER BY 1,2;

SELECT
      *,
      lag(retained_customers,1) OVER() AS lagged,
      (retained_customers - lag(retained_customers,1) OVER()) 
      / retained_customers * 100 AS diff
      -- here we obtain the retained_customers percentage
FROM retained_customers;