Mysql - Calculate Total, New and Lost Subscription based on Month

DhruvitSalat
May 23, 2023
202 views
0 votes
2 Answers

We have a table called "subscriptions" that stores information about salon subscriptions. The table has the following columns:

subscriptions

id, start_date, end_date, subscription_plan_id, salon_id

We want to write a query that provides the following information for each month:

Date
Total number of subscriptions (Running Total)
Number of new subscriptions
Number of lost subscriptions
Total number of subscriptions for each subscription plan (standard, grow, basic, and free)
Number of new subscriptions for each subscription plan (standard, grow, basic, and free)
Number of lost subscriptions for each subscription plan (standard, grow, basic, and free)

The total values should represent the cumulative count overall, excluding lost subscriptions for each month. The new and lost values should be specific to each month.

I got the result I wanted but using the queries below

SELECT Date.date,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                  THEN s.salon_id
                  END) AS total_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                  THEN s.salon_id
                  END) AS new_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                  THEN s.salon_id
                  END) AS lost_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 4
                  THEN s.salon_id
                  END) AS total_standard_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 3
                  THEN s.salon_id
                  END) AS total_grow_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 2
                  THEN s.salon_id
                  END) AS total_basic_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 1
                  THEN s.salon_id
                  END) AS total_free_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 4
                  THEN s.salon_id
                  END) AS new_standard_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 3
                  THEN s.salon_id
                  END) AS new_grow_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 2
                  THEN s.salon_id
                  END) AS new_basic_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 1
                  THEN s.salon_id
                  END) AS new_free_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 4
                  THEN s.salon_id
                  END) AS lost_standard_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 3
                  THEN s.salon_id
                  END) AS lost_grow_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 2
                  THEN s.salon_id
                  END) AS lost_basic_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 1
                  THEN s.salon_id
                  END) AS lost_free_subs
        
FROM(
     SELECT CONCAT(YEAR(start_date),'-', LPAD(MONTH(start_date), 2, '0')) AS date FROM subscriptions
     UNION 
     SELECT CONCAT(YEAR(start_date),'-', LPAD(MONTH(start_date), 2, '0')) AS date FROM subscriptions
) AS Date
CROSS JOIN subscriptions AS s
WHERE Date.date <= DATE_FORMAT(CURDATE(), '%Y-%m') 
GROUP BY Date. date
ORDER BY Date.date DESC;

I am looking for a more efficient and streamlined approach to achieve the same result.

Thanks For Help In Advance!

Answers

Chosen as BEST ANSWER


SELECT Date.date,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                  THEN s.salon_id
                  END) AS total_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                  THEN s.salon_id
                  END) AS new_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                  THEN s.salon_id
                  END) AS lost_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 4
                  THEN s.salon_id
                  END) AS total_standard_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 3
                  THEN s.salon_id
                  END) AS total_grow_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 2
                  THEN s.salon_id
                  END) AS total_basic_subs,
       COUNT(CASE WHEN Date.date BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m')
                                 AND s.subscription_plan_id = 1
                  THEN s.salon_id
                  END) AS total_free_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 4
                  THEN s.salon_id
                  END) AS new_standard_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 3
                  THEN s.salon_id
                  END) AS new_grow_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 2
                  THEN s.salon_id
                  END) AS new_basic_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(s.start_date, '%Y-%m')
                                 AND s.subscription_plan_id = 1
                  THEN s.salon_id
                  END) AS new_free_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 4
                  THEN s.salon_id
                  END) AS lost_standard_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 3
                  THEN s.salon_id
                  END) AS lost_grow_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 2
                  THEN s.salon_id
                  END) AS lost_basic_subs,
       COUNT(CASE WHEN Date.date = DATE_FORMAT(DATE_ADD(s.end_date, INTERVAL 1 MONTH), '%Y-%m')
                                 AND s.subscription_plan_id = 1
                  THEN s.salon_id
                  END) AS lost_free_subs
        
FROM(
     SELECT CONCAT(YEAR(start_date),'-', LPAD(MONTH(start_date), 2, '0')) AS date FROM subscriptions
     UNION 
     SELECT CONCAT(YEAR(start_date),'-', LPAD(MONTH(start_date), 2, '0')) AS date FROM subscriptions
) AS Date
CROSS JOIN subscriptions AS s
WHERE Date.date <= DATE_FORMAT(CURDATE(), '%Y-%m') 
GROUP BY Date. date
ORDER BY Date.date DESC;

This is the best possible we were able to achieve thanks to @lemons help

I am accepting this as a answer if we get any better answer in future i'll update it!

(Edit)

You could try following these steps:

replace the INNER JOIN with a CROSS JOIN
recreate the partitions with a CASE expression
apply conditional aggregation

WITH cte AS (
    SELECT CONCAT(MONTH_YEAR.Y,'-', LPAD(MONTH_YEAR.m, 2, '0')) AS date,
           CASE WHEN CONCAT(MONTH_YEAR.Y,'-', LPAD(MONTH_YEAR.m, 2, '0')) <= DATE_FORMAT(CURDATE(), '%Y-%m') 
                THEN CASE WHEN CONCAT(MONTH_YEAR.Y,'-', LPAD(MONTH_YEAR.m, 2, '0')) 
                               BETWEEN DATE_FORMAT(s.start_date, '%Y-%m') AND DATE_FORMAT(s.end_date, '%Y-%m') 
                          THEN 1
                          WHEN DATE_FORMAT(new.start_date, '%Y-%m') = CONCAT(MONTH_YEAR.Y,'-', LPAD(MONTH_YEAR.m, 2, '0'))
                          THEN 2
                     END
                WHEN DATE_FORMAT(DATE_ADD(lost.end_date, INTERVAL 1 MONTH), '%Y-%m') = CONCAT(Y, '-', LPAD(m, 2, '0'))
                 AND CONCAT(Y, '-', LPAD(m, 2, '0')) <= DATE_FORMAT(CURDATE(), '%Y-%m')
                THEN 3
           END AS parts,
           s_subscription_plan_id
    FROM (SELECT MONTH(start_date) AS m, YEAR(start_date) AS Y FROM subscriptions
          UNION 
          SELECT MONTH(end_date)   AS m, YEAR(end_date)   AS Y FROM subscriptions) AS MONTH_YEAR
    CROSS JOIN subscriptions AS s
)
SELECT date,
       SUM(parts = 1) AS total_subscriptions,
       SUM(parts = 2) AS new_subscriptions,
       SUM(parts = 3) AS lost_subscription,
       SUM(parts = 1 AND subscription_plan_id = 4) AS total_standard_subscription,
       SUM(parts = 1 AND subscription_plan_id = 3) AS total_grow_subscription,
       SUM(parts = 1 AND subscription_plan_id = 2) AS total_basic_subscription,
       SUM(parts = 1 AND subscription_plan_id = 1) AS total_free_subscription,
       SUM(parts = 2 AND subscription_plan_id = 4) AS new_standard_subscription,
       SUM(parts = 2 AND subscription_plan_id = 3) AS new_grow_subscription,
       SUM(parts = 2 AND subscription_plan_id = 2) AS new_basic_subscription,
       SUM(parts = 2 AND subscription_plan_id = 1) AS new_free_subscription,
       SUM(parts = 3 AND subscription_plan_id = 4) AS lost_standard_subscription,
       SUM(parts = 3 AND subscription_plan_id = 3) AS lost_grow_subscription,
       SUM(parts = 3 AND subscription_plan_id = 2) AS lost_basic_subscription,
       SUM(parts = 3 AND subscription_plan_id = 1) AS lost_free_subscription
FROM cte
GROUP BY date
ORDER BY date DESC

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Calculate Total, New and Lost Subscription based on Month

Answers