MySQL - users' monthly spend in the first 6 months of initial subscription purchase

Question

I am trying to create report that shows subscription price (AmountPerMonth) month by month for 6 months for all users - where Month1 is the date the user has purchased the 1st subscription date (NOT the registration date) , and Month2 etc are the subsequent months from that date, varying for each account.
The format of the table for this report

I have managed to pull the first 2 months table, but can't figure out how to continue up to the 6th month. Thank you in advance!

SELECT F1.Id, F1.Month1, F2.Month2
FROM
(SELECT Id, AmountPerMonth AS Month1, ActionDate
FROM MONTLYSPEND
GROUP BY ID
HAVING MIN(ActionDate)) AS F1,
(SELECT t1.Id R, t2.AmountPerMonth AS Month2, MIN(t2.ActionDate)
FROM MONTLYSPEND t1
INNER JOIN MONTLYSPEND t2
ON t1.Id = t2.Id
AND t1.ActionDate < t2.ActionDate
GROUP BY t1.Id) AS F2
WHERE F1.id = F2.R
;

Is it to say that there are columns such as `Month3`, `Month4`....`Month6` in the table? If so, then it's quite likely that the design is not [normalised](http://www.studytonight.com/dbms/database-normalization.php). — Dhruv Saxena, Apr 25 '17 at 15:35
@JenR - I have uploaded an image; DhruvSaxena - that's correct. — Lya1981, Apr 25 '17 at 16:42
@Lya1981 That is generally not the best way to design a scalable table structure. Not sure if you only have a maximum of 6 months. As one would imagine, if the data was to extend beyond 6 months, the table will need to have as many columns to accommodate it. Therefore, it might be worth considering to create a new table along the following lines: `months(user_id, payment_date, amount)`. It would help in adding as many months as needed for a user and thereby select records to the desired depth without writing overly complex queries. For eg.: [this](http://stackoverflow.com/q/12113699/2298301) — Dhruv Saxena, Apr 25 '17 at 16:57
@DhruvSaxena - thank you, yes, I did that already: MONTHLYSPEND table is a view I created that consists of exactly those 3 tables. I have tried different steps, like limiting it to 6 months per user, so if I sort it by userid I have six records per each; I then select the Min(subscriptiondate) as month1 - and its fine; then I use a self join for the second month, and it works, but it's based on date1 — Lya1981, Apr 26 '17 at 08:36
@DhruvSaxena I am currently looking into stored procedures - as I am not familiar with them, but hoping that might work, do you think that's the way to go? I assume I need to use loops on this, and something in the lines of mindate set to 0 (or default), and mindate = mindate +1 (will that take me to the next subsequent date row?) and looping it 6 times? — Lya1981, Apr 26 '17 at 08:43
Edit: MONTHLYSPEND table is a view I created that consists of exactly those 3 **columns** — Lya1981, Apr 26 '17 at 10:00
@Lya1981, Will it be possible for you to please amend the question to include: (1) The new table structure and some sample data, (2) The query you're trying (plus, the output you get with it) and (3) The desired output based on the sample data? Maybe using http://rextester.com or http://sqlfiddle.com/ ? Based on the comments, I also get a feeling that you're perhaps trying to pivot the table dynamically. It may be possible, but there's unfortunately no easy workaround either. Is this [Q&A](http://stackoverflow.com/q/12004603/2298301) of any interest at all? — Dhruv Saxena, Apr 26 '17 at 20:09

Lya1981 · Answer 1 · 2017-05-10T10:02:40.030

Turns out there were two ways - Stored Procedure, which takes too much memory, or using CASE WHEN, this pivots the table as well. Hope it's useful to people who have to generate reports showing various activity per user day by day or month by month on the x axis. The main difficulty I had was the fact that Month_1 (first purchased subscription) was a different date for every user. This report can be used to analyse your users behavior in the first 6 months of their subscription.
The report generated by this query looks like this:

+--------+----------+------------------+---------+---------+--------+
| UserId | Currency | FirstSubscrPurch | Month_1 | Month_2 | etc... |
+--------+----------+------------------+---------+---------+--------+
| 123    |   GBP    |  2010-05-27      |  34.00  |  27.00  |  0.00  |
+--------+----------+------------------+---------+---------+--------+

SELECT F6.USERID, F6.Currency, DATE_FORMAT(F6.FirstSubscrPurch, "%Y-%m-%d") AS FirstSubscrPurch, F6.MONTH_1, F6.MONTH_2,F6.MONTH_3, F6.MONTH_4, F6.MONTH_5, F6.MONTH_6, ROUND(((F6.MONTH_1+F6.MONTH_2+F6.MONTH_3+F6.MONTH_4+F6.MONTH_5+F6.MONTH_6)/6),2) AVERAGE, F6.CURRENCY

FROM (
    SELECT
        UserId, Currency, FirstSubscrPurch,
        SUM(CASE WHEN YEAR_AND_MONTH_INDEX = 0 THEN TOTAL_AMOUNT_PAID ELSE 0 END) MONTH_1,
        SUM(CASE WHEN YEAR_AND_MONTH_INDEX = 1 THEN TOTAL_AMOUNT_PAID ELSE 0 END) MONTH_2,
        SUM(CASE WHEN YEAR_AND_MONTH_INDEX = 2 THEN TOTAL_AMOUNT_PAID ELSE 0 END) MONTH_3,
        SUM(CASE WHEN YEAR_AND_MONTH_INDEX = 3 THEN TOTAL_AMOUNT_PAID ELSE 0 END) MONTH_4,
        SUM(CASE WHEN YEAR_AND_MONTH_INDEX = 4 THEN TOTAL_AMOUNT_PAID ELSE 0 END) MONTH_5,
        SUM(CASE WHEN YEAR_AND_MONTH_INDEX = 5 THEN TOTAL_AMOUNT_PAID ELSE 0 END) MONTH_6

        FROM (
            SELECT
                hp.UserId, hp.Currency, MIN(hp.Date) AS FirstSubscrPurch,
                CONCAT(YEAR(Date),'-',MONTH(Date)) AS YEAR_AND_MONTH,
                TIMESTAMPDIFF( MONTH, CONCAT(YEAR(FIRST_PAYMENT_DATE),'-',MONTH(FIRST_PAYMENT_DATE),'-1'), CONCAT(YEAR(Date),'-',MONTH(Date),'-1')) AS YEAR_AND_MONTH_INDEX, -- generates string in format YYYY-M-D
                MIN(Date) FIRST_PAYMENT_OF_MONTH, 
                MAX(Date) LAST_PAYMENT_OF_MONTH, 
                COUNT(*) NUM_PAYMENTS,
                SUM(hp.Amount) TOTAL_AMOUNT_PAID, 
                SUM(hp.Credits) Credits
                FROM payments hp
                JOIN (
                    SELECT UserId, MIN(Date) FIRST_PAYMENT_DATE, ADDDATE(MIN(Date), INTERVAL 6 MONTH) SIX_MONTHS_AFTER_FIRST_PAYMENT
                    FROM payments hp 
                    GROUP BY UserId
                    ) USER_MIN_ID ON USER_MIN_ID.UserId = hp.UserId 
                    AND hp.Date BETWEEN FIRST_PAYMENT_DATE AND CONCAT(YEAR(SIX_MONTHS_AFTER_FIRST_PAYMENT),'-',MONTH(SIX_MONTHS_AFTER_FIRST_PAYMENT),'-1')
                    GROUP BY UserId, Currency, YEAR_AND_MONTH
                ORDER BY hp.UserId, hp.Date
            ) F
        GROUP BY UserId, Currency
ORDER BY UserId DESC) F6;

MySQL - users' monthly spend in the first 6 months of initial subscription purchase

1 Answers1