Fiddle in this link.

I have a table with user_id and action_time: Schema (PostgreSQL v13)

CREATE TABLE actions(
  action_time timestamp,
  user_id int
  );
  
insert into actions values ('2015-01-20 01:00',1);
insert into actions values ('2015-01-01 01:00',1);
insert into actions values ('2015-01-10 01:00',1);
insert into actions values ('2015-01-12 01:00',1);
insert into actions values ('2015-01-16 01:00',1);
insert into actions values ('2015-01-23 01:00',1);
insert into actions values ('2015-02-20 01:00',1);
insert into actions values ('2015-03-20 01:00',1);
insert into actions values ('2015-05-20 01:00',1);
insert into actions values ('2015-06-20 01:00',1);
insert into actions values ('2015-01-20 01:00',2);
insert into actions values ('2015-03-20 01:00',2);
insert into actions values ('2015-04-20 01:00',2);
insert into actions values ('2015-05-20 01:00',2);
insert into actions values ('2015-05-21 01:00',2);
insert into actions values ('2015-05-21 01:00',2);
insert into actions values ('2015-05-23 01:00',2);

I want to check if a user has done at least one action every month for 6 months.

By using this I get how many actions a user has done every month:

SELECT date_trunc('month',action_time) AS month_parth, user_id,COUNT(*) AS 
monthly_actions FROM actions
GROUP BY month_parth,user_id
ORDER BY user_id,month_parth ASC;

And obtain the following:

Query #1

SELECT date_trunc('month',action_time) AS month_parth, user_id,COUNT(*) AS monthly_actions FROM actions
GROUP BY month_parth,user_id
ORDER BY user_id,month_parth ASC;

The user_id 1 has clearly at least one action per month (peaking at 6 on January), but the 2 has some gaps.

I am not sure how to go here, if I would need to do a new query grouping and using HAVING or how to move next.

The desired result could be a simple dummy column with TRUE/FALSE for the users that have at least one action per month during 6 months.

CodePudding user response：

Here is a query where one_action_per_month_during_next_6_months is a boolean per user_id and per month which is true if the user executed at least one action in the next comming months :

WITH list AS
(
SELECT date_trunc('month',action_time) AS month_parth
     , user_id, COUNT(*) AS monthly_actions
  FROM actions
 GROUP BY month_parth,user_id
)
SELECT user_id
     , month_parth
     , monthly_actions
     , count(*) FILTER (WHERE monthly_actions >=1) OVER (PARTITION BY user_id ORDER BY month_parth RANGE BETWEEN CURRENT ROW AND '6 months' FOLLOWING) = 6 AS one_action_per_month_during_next_6_months
  FROM list AS l
 ORDER BY user_id,month_parth ASC

test results in dbfiddle

CodePudding user response：

select user_id, count(cnt) >= 6 six_months from
(
 select user_id, date_trunc('month', action_time) monthperiod, count(*) cnt
 from actions
 group by user_id, monthperiod
) t
group by user_id;

BTW user 1 does not have 6 months with at least one action as he is missing April.