How to Calculate Cumulative paying MRR using SQL?
I have a table which contains the following data:
Customer_ID | Date | MRR_Change
MRR_Change is the incremental revenue change in the customer’s subscription over and above the previous transaction. So If a customer buys a plan for $20/mo. and then upgrades to a plan which is $30/mo., the MRR_change for this transaction will be $30-$20 = $10/mo.
*Until the next transaction occurs, you can assume the customer is paying what he/she was paying as off the last transaction. For instance, if the latest transaction was Upgrade on plan 11 –> we can assume the customer is still currently active on plan 11
We want to calculate the cumulative paying MRR for every customer as of 01/01/2018.
Input:
+-------------+-------------------+------------+ | Customer_ID | Txn_date | MRR_Change | +-------------+-------------------+------------+ | 190290 | 01 April 2016 | 260 | | 190290 | 01 May 2016 | 31 | | 190290 | 01 July 2016 | -76 | | 190290 | 01 September 2016 | 260 | | 190290 | 01 October 2016 | -260 | +-------------+-------------------+------------+ Output:
+-------------+-----------------------+ | Customer_ID | Cumulative_Paying_MRR | +-------------+-----------------------+ | 190290 | $ 4,972 | +-------------+-----------------------+ 
You would sum the change column as of the transaction date you’re concerned with, grouping by the customer ID.
Refraining from writing SQL because of how strongly this resembles homework.
Welcome to mysql on SO!
@chaos is pointing you in the right direction, but the answer is slightly incomplete.
It’s not enough to sum the changes. You need to know how many transactions have been posted with that amount. This is not trivial from the data you have provided.
For each change you need to know hopw many payments have been made since that date, you then multiply the change by the number of payments, and sum this amount.
How do you find the number of payments? Assuming there is one payment per month then you need to calculate the number of months between your end date and the change date. You can use the MySQL function PERIOD_DIFF for that – but you will need to get the arguments into the correct format as it doesn’t take dates. If your column TxnDate is actually a Date then you can use the DATE_FORMAT function to create the arguments in the format you need. If it’s not a date then you will have to convert it to a date before you can use the DATE_FORMAT function, or you could convert directly to the format needed for PERIOD_DIFF.
Note that PERIOD_DIFF gives you the number of periods between the two dates. If you want to include both (which seems to be what you need) you will need to add 1 to the result.
So you will need something like:
SELECT Customer_ID, SUM((PERIOD_DIFF(<arg1>, <arg2>)+1)*MRR_Change) FROM <TableName> GROUP BY Customer_ID Clearly you need to specify the appropriate arguments for the PERIOD_DIFF function, and of course I can’t guarantee to be typo free!
This should be enough to point you at the right answer,
The best way to calculate cumulative sum (called sometimes "rolling sum") in SQL is with window function:
WITH t1 (date,count_events) AS ( SELECT * FROM (VALUES ('2020-01-10',3),('2020-01-11',4),('2020-01-13',1),('2020-01-17',2),('2020-01-22',5))) SELECT *, sum(count_events) over (order by date) cumulative_sum FROM t1 ORDER BY date Results date count_events cumulative_sum 2020-01-10 3 3 2020-01-11 4 7 2020-01-13 1 8 2020-01-17 2 10 2020-01-22 5 15 This example is from Presto, for each line it calculates the sum form begining of table till current line. There are many depth details but this is the basic.
Many sites have explanations, like this one and many many more..
Please find solution below:
DECLARE @FromDate DATETIME, @ToDate DATETIME; SET @FromDate = '2015-01-01'; SET @ToDate = '2018-12-31'; ;with date_dimension as( SELECT TOP (DATEDIFF(MONTH, @FromDate, @ToDate)+1) TheDate = DATEADD(MONTH, number, @FromDate) FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number), distinct_customer as( SELECT DISTINCT Group_ID from Sheet1$), date_customer as( SELECT * from date_dimension a, distinct_customer b), date_customer_mrr as( select a.TheDate, a.Group_ID, coalesce(b.MRR_Change,0) as MRR_Change from date_customer a left join Sheet1$ b on a.TheDate = b.Txn_date) select a.Group_ID, sum(a.cumulative_paying_mrr) as cumulative_paying_mrr from (select Group_ID, sum(MRR_Change) over(order by TheDate asc rows between unbounded preceding and current row) cumulative_paying_mrr from date_customer_mrr where TheDate < '2018-01-01 00:00:00.000') a group by a.Group_ID 
