I am using mysql community server version 8.0.22
Summary -
- I would like to calculate Month-on-Month as follow for existing data
- I would like to add "trigger/stored procedure", for following months after I upload new month data, it should also update calculation in "MoM_calculation" table
total distinct count of - loyal customers ( customers from prev-month continue doing transaction in following month)
total distinct count of - dropout customers ( customers from prev-month no longer present in following month)
total distinct count of - new customers ( customers did not do transaction in prev-month but appears in following month)
SQL Fiddle , I have prepared sql statements for table create and insert data in fiddle.
Desired Output result table "MoM_calculation"
| year | month | total_customer | loyal | new | dropout |
|----- | ----- | -------------- | ----- | --- | ------- |
| 2021 | 01 | 5 | 0 | 5 | 0 |
| 2021 | 02 | 8 | 3 | 5 | 2 |
| 2021 | 03 | 7 | 1 | 6 | 7 |
| 2021 | 04 | 5 | 3 | 2 | 4 |
| 2021 | 05 | 5 | 0 | 5 | 5 |
| 2021 | 06 | 7 | 5 | 2 | 0 |
Here is Example of Daily Transaction Table
| _date | customer_id | total |
|----------|-------------|-------|
|2021-01-13| c1 | 10.0 |
|2021-01-13| c2 | 20.0 |
|2021-01-14| c3 | 10.0 |
|2021-01-15| c4 | 14.0 |
|2021-01-16| c1 | 20.0 |
|2021-01-17| c5 | 15.0 |
|2021-02-13| c1 | 10.0 |
|2021-02-13| c2 | 20.0 |
|2021-02-14| c3 | 10.0 |
|2021-02-15| c6 | 14.0 |
|2021-02-16| c7 | 20.0 |
|2021-02-17| c8 | 15.0 |
|2021-02-17| c9 | 13.0 |
|2021-02-18| c10 | 15.0 |
|2021-02-19| c10 | 15.0 |
CodePudding user response：
The first thing that comes to mind is that there is a lot of noise in the transaction table for this task. We see every single transaction by the same customer in the same month, while it suffices to know that the customer had at least one transaction in the month. But it's easy to boil that down:
select distinct year(_date), month(_date), customer_id from transactions;
Then we want to check whether the same customer had a transaction in the previous month. But while for 2021-04 we can just subtract 1 from the month and get 2021-03, we cannot do so for 2021-01, because there the month would have to switch from 1 to 12 and the year would have to change, too, in order to get to 2020-12. This makes joining the months difficult, but is also easy to solve. If we simply multiply a year by twelve and add the month we get the number 24252 for 2020-12 and the number 24253 for 2021-01. Adjacent numbers we can easily compare.
Then, we want a full outer join; customers present only in the previous month, customers present only in the current month, customers present in both months. But MySQL does not support full outer joins. The solution to this is to select all months and all customers and join on these combinations. This makes the query look a little clumsy, but it works.
with data as
(
select distinct
year(_date) as year,
month(_date) as month,
year(_date) * 12 month(_date) as num,
customer_id
from transactions
)
, months as (select distinct year, month, num from data)
, customers as (select distinct customer_id from data)
select
m.year,
m.month,
count(this_month.num) as total_count,
sum(prev_month.num is not null and this_month.num is not null) as loyal_ones,
sum(prev_month.num is null and this_month.num is not null) as new_ones,
sum(prev_month.num is not null and this_month.num is null) as dropouts
from months m
cross join customers c
left join data this_month on this_month.customer_id = c.customer_id and this_month.num = m.num
left join data prev_month on prev_month.customer_id = c.customer_id and prev_month.num = m.num - 1
group by m.year, m.month
order by m.year, m.month;
Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=199705ebab03a2f09254654d4d814944