Sample Header Ad - 728x90

Running sum total over 14 days for each date

1 vote
1 answer
725 views
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
Asked by Darrel (11 rep)
Nov 11, 2020, 10:37 AM
Last activity: Nov 12, 2020, 05:02 PM