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

Solution 1:

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