Mysql – Running sum total over 14 days for each date

MySQLrunning-totals

In a database of transactions spanning about 50 customers over a period of 8 months, I need to find which customers have 0 total transactions over a consecutive 14-day period. The customer table (customer_tbl) has the following general structure:

cust_id, date, new_transactions, total_transactions

So for every row, I want a column where it shows the total number of transactions that the customer had, for the past 14 days. I have tried to create a view to display customers who had 0 total transactions using the code below.

create view sumoftransactions as SELECT
cust_id
, date
, REVERSE_RUNNING_SUM
FROM
(
    SELECT 
      cust_id
    , date
    , SUM(new_transactions) OVER (PARTITION BY cust_id ORDER BY date DESC, VALID_FLAG ASC) REVERSE_RUNNING_SUM
    , VALID_FLAG
    FROM 
    (
        SELECT cust_id
        , date
        , new_transactions
        , 1 VALID_FLAG
        FROM customer_tbl

        UNION ALL

        SELECT cust_id
        , DATE_ADD(date, interval -14 day) date
        , -1 * new_transactions
        , -1 VALID_FLAG
        FROM customer_tbl
    ) t
) tt
WHERE tt.VALID_FLAG = 1
and tt.reverse_running_sum = 0;

However the output is skewed. I believe it has something to do with the cut-off dates. Each customer has a different date for their first record. The running sum formula includes the record of their first transaction date into the output because there are no dates before this, so the running sum is calculated to be 0, even though it is not.

How do I rectify this code? The following is some sample data. To simplify it, the following table only has 3 customers and covers a period of one week. I want to generate the customer_id and dates whereby there have been at least 3 consecutive days of 0 transactions. The desired output is written below.

CREATE TABLE `customer_tbl` (
  `cust_id` text,
  `date` date default null,
  `new_transactions` text,
  `total_transactions` text
)

INSERT INTO `customer_tbl` VALUES 
(‘10001’, ‘2020-02-01’, ‘1’, ‘1’),
(‘10001’, 2020-02-02’, ‘1’, ‘2’),
(‘10001’, ‘2020-02-03’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-03’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-04’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-05’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-06’, ‘1’, ‘3’),
(‘10002’, ‘2020-02-01’, ‘1’, ‘1’),
(‘10002’, 2020-02-02’, ‘1’, ‘2’),
(‘10002’, ‘2020-02-03’, ‘4’, ‘6’),
(‘10002’, ‘2020-02-03’, ‘0’, ‘6’),
(‘10002’, ‘2020-02-04’, ‘1’, ‘7’),
(‘10002’, ‘2020-02-05’, ‘0’, ‘7’),
(‘10002’, ‘2020-02-06’, ‘1’, ‘8’),
(‘10003’, ‘2020-02-01’, ‘0’, ‘0’),
(‘10003’, 2020-02-02’, ‘0’, ‘0’),
(‘10003’, ‘2020-02-03’, ‘1’, ‘1’),
(‘10003’, ‘2020-02-03’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-04’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-05’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-06’, ‘1’, ‘2’);

desired output

Best Answer

This is a bit tricky but very fast query that counts how many days customer spent idle up to the date. You can use it as subquery to refine by additional conditions. I hope it is selfexplanatory enough.

Unfortunately dbfiddle works pretty bad with UDVs so I can't provide a working sample.

SELECT cust_id
     , date
     , new_transactions
     , total_transactions
     , IF( @curr_cust_id <=> @curr_cust_id := cust_id,
       /*THEN*/ IF( new_transactions = 0,
                /*THEN*/ @idle4 := @idle4 + 1,
                /*ELSE*/ @idle4 := 0 
                /*FI*/ ), 
       /*ELSE*/ @idle4 := 0
       /*FI*/ ) AS idle_for
  FROM customer_tbl
 WHERE ( TRUE OR @idle4 := 0 )       -- UDV initialization
 ORDER BY cust_id ASC
        , date ASC
;

+---------+------------+------------------+--------------------+----------+
| cust_id |       date | new_transactions | total_transactions | idle_for |
+---------+------------+------------------+--------------------+----------+
|   10001 | 2020-02-01 |                1 |                  1 |        0 |
|   10001 | 2020-02-02 |                1 |                  2 |        0 |
|   10001 | 2020-02-03 |                0 |                  2 |        1 |
|   10001 | 2020-02-03 |                0 |                  2 |        2 |
|   10001 | 2020-02-04 |                0 |                  2 |        3 |
|   10001 | 2020-02-05 |                0 |                  2 |        4 |
|   10001 | 2020-02-06 |                1 |                  3 |        0 |
Related Question