SQL query for plotting a monthly retention grid for orders

monthly_retention_percentage_orders.sql

SELECT 
	activity_sq.activity_cohort_date AS first_purchase_date,
	population_agg_sq.cohort_total_user_count AS total_user_count,
	period_diff(activity_sq.activity_date, activity_sq.activity_cohort_date) AS month_number,
	count(DISTINCT activity_sq.order_user_id) * 100 / population_agg_sq.cohort_total_user_count AS retention_percent 
FROM (
	    SELECT date_format(convert_tz(order.paid_on, '+00:00', '+05:30'), '%Y%m') AS activity_date, 
	    	   order.user_id AS order_user_id, 
	    	   population_sq.cohort_date AS activity_cohort_date 
	    FROM 
	    	(SELECT date_sub(now(), INTERVAL {{months}} MONTH) AS interval_start) AS anon_3,
	         order INNER JOIN (
	            SELECT 
	            	date_format(convert_tz(user.first_order_paid_on, '+00:00', '+05:30'), '%Y%m') AS cohort_date,
	            	user.id AS user_id 
	            FROM 
	            	user,
	            	(SELECT date_sub(now(), INTERVAL {{months}} MONTH) AS interval_start) AS anon_3 
	            WHERE date(convert_tz(user.first_order_paid_on, '+00:00', '+05:30')) > anon_3.interval_start
	            AND user.first_order_paid_on IS NOT NULL
	        	) AS population_sq ON population_sq.user_id = order.user_id 
	    WHERE date(convert_tz(order.paid_on, '+00:00', '+05:30')) > anon_3.interval_start 
	    AND order.is_paid = true
	    AND order.repetition_degree > 1
	    AND order.user_id IS NOT NULL
    ) AS activity_sq 
INNER JOIN 
(SELECT 
	population_sq.cohort_date AS population_agg_cohort_date, 
	count(DISTINCT population_sq.user_id) AS cohort_total_user_count 
	FROM (
		SELECT date_format(convert_tz(user.first_order_paid_on, '+00:00', '+05:30'), '%Y%m') AS cohort_date, 
		user.id AS user_id 
		FROM user, 
		(SELECT date_sub(now(), INTERVAL {{months}} MONTH) AS interval_start) AS anon_3 
		WHERE date(convert_tz(user.first_order_paid_on, '+00:00', '+05:30')) > anon_3.interval_start 
		AND user.first_order_paid_on IS NOT NULL
		) AS population_sq 
	GROUP BY population_agg_cohort_date
) AS population_agg_sq 
ON population_agg_sq.population_agg_cohort_date = activity_sq.activity_cohort_date 
GROUP BY first_purchase_date, month_number, population_agg_sq.cohort_total_user_count