Count Unique Data With Minimum Date Postgres-CodePudding

I got a problem while trying to count data in my table.

for example my table look like this:

table = test
id       | transaction_date
1        | '2021-10-01 00:00:00'
2        | '2021-10-01 00:00:00'
3        | '2021-10-02 00:00:00'
4        | '2021-10-03 00:00:00'
5        | '2021-10-04 00:00:00'
6        | '2021-10-05 00:00:00'
7        | '2021-10-06 00:00:00'

1        | '2021-11-01 00:00:00'
2        | '2021-11-01 00:00:00'
3        | '2021-11-02 00:00:00'

1        | '2021-12-01 00:00:00'
2        | '2021-12-01 00:00:00'
3        | '2021-12-02 00:00:00'
4        | '2021-12-03 00:00:00'
8        | '2021-12-04 00:00:00'
8        | '2021-12-05 00:00:00'
9        | '2021-12-06 00:00:00'
9        | '2021-12-07 00:00:00'

I tried to use this query

WITH first_transaction_AS (SELECT MIN(transaction_date), id FROM test),
previous_month AS (SELECT DISTINCT idFROM test 
WHERE transaction_date >= date_trunc('month', transaction_date)-interval '1 month' 
AND transaction_date < date_trunc('month', transaction_date)

-- WHERE clause for previous month will be dynamic

SELECT date_trunc('month', transaction_date), COUNT(DISTINCT id)
FROM test 
WHERE test.id NOT IN (SELECT id FROM previous_month) AND transaction_date NOT IN (SELECT min FROM first_transaction)

I wanted to achieve a aggregate query where: Count ID Where ID has Transaction this month AND ID Without Transaction Last Month AND It's not the ID's First Transaction

The current problem is, ID 8 and 9 Will still be count Because they has a transaction after their first transaction in the same month.

So what i aim for the result is:

    Date                    |         Count
  2021-12-01 00:00:00       |           1  (4)

CodePudding user response：

Only 1 according the rules.

select 
  date(date_trunc('month', transaction_date)) as "Date"
, count(distinct id) as "Count"
from test t
where transaction_date >= date_trunc('month', current_date)
  and transaction_date < (date_trunc('month', current_date) interval '1 month') 
  and not exists ( -- not previous month
      select 1
      from test t2
      where t2.id = t.id
        and t2.transaction_date >= date_trunc('month', current_date) - interval '1 month'
        and t2.transaction_date <  date_trunc('month', current_date)
  
  )
  and exists ( -- created previously
      select 1
      from test t2
      where t2.id = t.id
        and t2.transaction_date <  date_trunc('month', current_date)
  )
group by date_trunc('month', transaction_date)

Date       | Count
:--------- | ----:
2021-12-01 |     1

Demo on db<>fiddle here