Identifying who spent more than a certain amount within any 30 day period?-CodePudding

I have a table that lists each customer's transactions along with the date they occurred and how much was spent. What I want to do is get a list of all customers who spent £3k or more within any 30-day period.

I can get a list of who spent £3k or more within the last 30 days using the code below, but I'm not sure how to adapt this to cover any 30-day period. Any help would be appreciated please!

select  *
from
        (
        select      customer_id, sum(spend) as total_spend
        from        transaction_table
        where       transaction_date between (current date - 30 days) and current date
        group by    customer_id
        )
where   total_spend >=3000
;

CodePudding user response：

You can use SUM() with a window function and a window frame of 30. For example:

select *
from (
  select t.*,
    sum(t.spent) over(
      partition by customer_id 
      order by julian_day(transaction_date)
      range between 30 preceding and current row
    ) as total_spend
  from transaction_table t
) x
where total_spend >= 3000

For the data set:

 CUSTOMER_ID  TRANSACTION_DATE  SPENT 
 ------------ ----------------- ----- 
 1            2021-10-01        2000  
 1            2021-10-15        1500  
 1            2021-12-01        1000  
 2            2021-11-01        2500

Result:

 CUSTOMER_ID  TRANSACTION_DATE  SPENT  TOTAL_SPEND 
 ------------ ----------------- ------ ----------- 
 1            2021-10-15        1500   3500

See running example at db<>fiddle.

CodePudding user response：

Try the following.
The idea is to calculate running sum of SPEND for last 30 days for each row.

WITH TRANSACTION_TABLE (CUSTOMER_ID, TRANSACTION_DATE, SPEND) AS
(
VALUES
  (1, DATE ('2021-01-01'), 1000)
, (1, DATE ('2021-01-31'), 2000)
--, (1, DATE ('2021-02-01'), 2000)
)
SELECT DISTINCT CUSTOMER_ID
FROM
(
SELECT 
  CUSTOMER_ID
--, TRANSACTION_DATE, SPEND
, SUM (SPEND) OVER (PARTITION BY CUSTOMER_ID ORDER BY DAYS (TRANSACTION_DATE) RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) AS SPEND_RTOTAL
FROM TRANSACTION_TABLE
)
WHERE SPEND_RTOTAL >= 3000