Nsryjdtyk

The below query returns all USERS that have SUM(AMOUNT) > 10 in a given month. It includes Users in a month even if they don't meet the criteria in other months.

But I'd like to transform this query to return all USERS who must meet the criteria SUM(AMOUNT) > 10 every single month (i.e., from the first month in the table to the last one) across the entire data.

Put another way, exclude users who don't meet SUM(AMOUNT) > 10 every single month.

select USERS, to_char(transaction_date, 'YYYY-MM') as month

from Table

GROUP BY USERS, month

HAVING SUM(AMOUNT) > 10;

One approach uses a generated calendar table representing all months in your data set. We can left join this calendar table to your current query, and then aggregate over all months by user:

WITH months AS (

    SELECT DISTINCT TO_CHAR(transaction_date, 'YYYY-MM') AS month

    FROM yourTable

),

cte AS (

    SELECT USERS, TO_CHAR(transaction_date, 'YYYY-MM') AS month

    FROM yourTable

    GROUP BY USERS, month

    HAVING SUM(AMOUNT) > 10

)



SELECT

    t.USERS

FROM months m

LEFT JOIN cte t

    ON m.month = t.month

GROUP BY

    t.USERS

HAVING

    COUNT(t.USERS) = (SELECT COUNT(*) FROM months);

The HAVING clause above asserts that the number of months to which a user matches is in fact the total number of months. This would imply that the user meets the sum criteria for every month.

Perhaps you could use a correlated subquery, such as:

select t.*

from (select distinct table.users from table) t

where not exists 

(

    select to_char(u.transaction_date, 'YYYY-MM') as month 

    from table u 

    where u.users = t.users 

    group by month

    having sum(u.amount) <= 10

)

One option would be using sign(amount-10) vs. sign(amount) logic as

SELECT q.users

  FROM

    (

    with tab(users, transaction_date,amount) as

    (

     select 1,date'2018-11-24',8 union all

     select 1,date'2018-11-24',18 union all    

     select 2,date'2018-10-24',13 union all

     select 3,date'2018-11-24',18 union all    

     select 3,date'2018-10-24',28 union all

     select 3,date'2018-09-24', 3 union all    

     select 4,date'2018-10-24',28

    )

    SELECT users, to_char(transaction_date, 'YYYY-MM') as month, 

           sum(sign(amount-10)) as cnt1,

           sum(sign(amount)) as cnt2

      FROM tab t

     GROUP BY users, month

    ) q

  GROUP BY q.users  

  HAVING sum(q.cnt1) = sum(q.cnt2)

  GROUP BY q.users



  users

  -----

    2

    4

Rextester Demo

You need to compare the number of months > 10 to the number of months between the min and the max date:

SELECT users, Count(flag) AS months, Min(mth), Max(mth)

FROM

 (

   SELECT users, date_trunc('month',transaction_date) AS mth, 

      CASE WHEN Sum(amount) > 10 THEN 1 end AS flag

   FROM tab t

   GROUP BY users, mth

 ) AS dt

GROUP BY users  

HAVING -- adding the number of months > 10 to the min date and compare to max

   Min(mth) + (INTERVAL '1' MONTH * (Count(flag)-1)) = Max(mth)

If missing months don't count it would be a simple count(flag) = count(*)