How do I get transactions amount > 1000 of all months in SQL-CodePudding

I have been trying to pull customers who have transaction amount greater than 1000 in all months. This is what I have tried so far. But it doesn't look like it's working when I do individual customer test.

Select customer 
,extract(month from trans_date) as mth
,extract(year from trans_date) as yr
,sum(trans_amount) as amt
, case when mth in (8) and amt > 1000 then 1 else 0 end as aug
, case when mth in (9) and amt > 1000 then 1 else 0 end as sep
, case when mth in (10) and amt > 1000 then 1 else 0 end as oct
, case when mth in (11) and amt > 1000 then 1 else 0 end as nov
, case when mth in (12) and amt > 1000 then 1 else 0 end as de_c

from transaction 
group by 1,2,3
having (aug = 1 and sep = 1 and oct=1 and nov=1 and de_c = 1)

CodePudding user response：

Select customer 
  ,extract(month from trans_date) as mth
  ,extract(year from trans_date) as yr
  ,sum(trans_amount) as amt
from transaction
-- filter only those months you want to check, e.g.
where trans_date between date '2021-08-01' and date '2021-12-31' 
group by 1,2,3
-- check that every month there was an individual transaction over 1000
qualify 
   min(max(trans_amount))
   over (partition by customer) > 1000

Edit:

Same logic to get just the customer without detail rows:

select customer
from 
 (
    Select customer, max(trans_amount) as maxamt
    from transaction
    -- filter only those months you want to check, e.g.
    where trans_date between date '2021-08-01' and date '2021-12-31' 
    group by 
       customer
      ,trunc(trans_date, 'mon') -- for every month
 ) as dt
group by customer
-- check that every month there was an individual transaction over 1000
having min(maxamt) > 1000

CodePudding user response：

You may want to try using Over (partition by) something like this.

Select customer 
,extract(month from trans_date) as mth
,extract(year from trans_date) as yr
,sum(trans_amount) over (partition by customer , extract(month from trans_date)) as 
total
From transaction 
Order by total desc