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.
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? Many thanks!
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;```