Count if previous month data exists postgres-CodePudding

i'm stuck with a query to count id where if it exists in previous month than 1

my table look like this

date      |      id       |
2020-02-02|       1       |
2020-03-04|       1       |
2020-03-04|       2       |
2020-04-05|       1       |
2020-04-05|       3       |
2020-05-06|       2       |
2020-05-06|       3       |
2020-06-07|       2       |
2020-06-07|       3       |

i'm stuck with this query

SELECT date_trunc('month',date), id
FROM table
WHERE id IN 
(SELECT DISTINCT id FROM table WHERE date 
BETWEEN date_trunc('month', current_date) - interval '1 month' AND date_trunc('month', current_date)

the main problem is that i stuck with current_date function. is there any dynamic ways change current_date? thanks

What i expected to be my result is

date      |      count    |
2020-02-01|       0       |
2020-03-01|       1       |
2020-04-01|       1       |
2020-05-01|       1       |
2020-06-01|       2       |

CodePudding user response：

Solution 1 with SELF JOIN

SELECT date_trunc('month', c.date) :: date AS date
     , count(DISTINCT c.id) FILTER (WHERE p.date IS NOT NULL)
  FROM test AS c
  LEFT JOIN test AS p
    ON c.id = p.id
   AND date_trunc('month', c.date) = date_trunc('month', p.date)   interval '1 month'
 GROUP BY date_trunc('month', c.date)
 ORDER BY date_trunc('month', c.date)

Result :

date        count
2020-02-01  0
2020-03-01  1
2020-04-01  1
2020-05-01  1
2020-06-01  2

Solution 2 with WINDOW FUNCTIONS

SELECT DISTINCT ON (date) date
     , count(*) FILTER (WHERE count > 0 AND previous_month) OVER (PARTITION BY date)
  FROM
     ( SELECT DISTINCT ON (id, date_trunc('month', date))
              id
            , date_trunc('month', date) AS date
            , count(*) OVER w AS count
            , first_value(date_trunc('month', date)) OVER w = date_trunc('month', date) - interval '1 month' AS previous_month
         FROM test
       WINDOW w AS (PARTITION BY id ORDER BY date_trunc('month', date) GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING)
     ) AS a

Result :

date        count
2020-02-01  0
2020-03-01  1
2020-04-01  1
2020-05-01  1
2020-06-01  2

see dbfiddle