How can I get all user_id values from the data below, for all rows containing the same user_id value over consecutive months from a given start date in the date column.

For example, given the below table....

...supposing I want to get the user_id values for consecutive months prior to (but not including) 2019-01-01 then I'd have this as my output:

probably can be applied windows function

CodePudding user response：

If you want to aggregate on a user and the year-months

select 
 t.user_id, 
 to_char(date_trunc('month',t.date),'YYYY-MM') as m_year
from yourtable t
where t.date < '2019-02-01'::date
group by t.user_id, date_trunc('month',t.date)
order by t.user_id, m_year

But if you only want those with consecutive months, then a little extra is needed.

select 
 user_id, 
 to_char(ym,'YYYY-MM') as m_year
from
(
  select t.user_id
  , date_trunc('month',t.date) as ym
  , lag(date_trunc('month',t.date)) 
   over (partition by t.user_id order by date_trunc('month',t.date)) as prev_ym
  , lead(date_trunc('month',t.date)) 
   over (partition by t.user_id order by date_trunc('month',t.date)) as next_ym
  from yourtable t
  where t.date < '2019-02-01'::date
  group by t.user_id, date_trunc('month',t.date)
) q
where (ym - prev_ym <= '31 days'::interval or 
       next_ym - ym <= '31 days'::interval)
order by user_id, ym

user_id | m_year 
------: | :------
     13 | 2018-11
     13 | 2018-12
     13 | 2019-01

db<>fiddle here

CodePudding user response：

you don't need a window function in this specific query. Just try :

SELECT DISTINCT ON (user_id) user_id, date_trunc('month', date :: date) AS m_year
  FROM your_table