I have a dataset like below.

For a particular ID, I need to compare the previous rows to check if they exist in the previous months. So my output table should like below:

I can use the LAG function but I think that one only works for one row before. For a particular ID, I need to look back 3 months if the ID exist or not. Don't need to do any days calculation just month is fine.

Is there any way I can achieve this using sql? Appreciate any help on this. Thanks.

CodePudding user response：

Following is based only on month calculations -

Actual table -

select * from PREV_MONTH;
 ----- ------------ 
|  ID | DT_DATE    |
|----- ------------|
| 100 | 2022-03-01 |
| 100 | 2022-02-10 |
| 100 | 2021-12-15 |
| 100 | 2021-11-30 |
| 200 | 2021-08-05 |
| 200 | 2021-07-10 |
 ----- ------------ 

Query to draw needed result -

select id,dt_date,
CASE
when extract(month from add_months(dt_date,-1)) 
IN 
(select extract (month from dt_date) FROM 
prev_month pm1 where pm1.id=prev_month.id)
    then 1
    else 0 end as month_1,
    CASE
    when extract(month from add_months(dt_date,-2)) 
IN 
(select extract (month from dt_date) FROM 
prev_month pm1 where pm1.id=prev_month.id)
    then 1
    else 0 end as month_2,
    CASE
    when extract(month from add_months(dt_date,-3)) 
IN 
(select extract (month from dt_date) FROM 
prev_month pm1 where pm1.id=prev_month.id)
    then 1
    else 0 end as month_3
    from prev_month;
 ----- ------------ --------- --------- --------- 
|  ID | DT_DATE    | MONTH_1 | MONTH_2 | MONTH_3 |
|----- ------------ --------- --------- ---------|
| 100 | 2022-03-01 |       1 |       0 |       1 |
| 100 | 2022-02-10 |       0 |       1 |       1 |
| 100 | 2021-12-15 |       1 |       0 |       0 |
| 100 | 2021-11-30 |       0 |       0 |       0 |
| 200 | 2021-08-05 |       1 |       0 |       0 |
| 200 | 2021-07-10 |       0 |       0 |       0 |
 ----- ------------ --------- --------- --------- 

CodePudding user response：

So with this CTE for data:

with data(ID, DT_DATE) as (
    SELECT * FROM VALUES
        (100, '2022-03-01'),
        (100, '2022-02-10'),
        (100, '2021-12-15'),
        (100, '2021-11-30'),
        (200, '2021-08-05'),
        (200, '2021-07-10')
)

Prior month window from each date:

This super NOT high performant code works, if you want to know "there were 1 values, in the 1,2,3 month window from this date" for each row.

SELECT 
    a.id
    ,a.dt_date
    ,count_if(b.dt_date >= dateadd('month',-1, a.dt_date) and b.dt_date < dateadd('month', 0, a.dt_date))::int as "1monthago"
    ,count_if(b.dt_date >= dateadd('month',-2, a.dt_date) and b.dt_date < dateadd('month', -1, a.dt_date))::int as "2monthago"
    ,count_if(b.dt_date >= dateadd('month',-3, a.dt_date) and b.dt_date < dateadd('month', -2, a.dt_date))::int as "3monthago"
FROM data AS a
LEFT JOIN data AS b 
    ON a.id = b.id AND b.dt_date < a.dt_date
GROUP BY 1,2
ORDER BY 1,2 desc;

Prior named month:

SELECT 
    a.id
    ,a.dt_date
    ,count_if(date_trunc('month', dateadd('month',-1, a.dt_date)) = date_trunc('month', b.dt_date))::int as "1monthago"
    ,count_if(date_trunc('month', dateadd('month',-2, a.dt_date)) = date_trunc('month', b.dt_date))::int as "2monthago"
    ,count_if(date_trunc('month', dateadd('month',-3, a.dt_date)) = date_trunc('month', b.dt_date))::int as "3monthago"
FROM data AS a
LEFT JOIN data AS b 
    ON a.id = b.id AND b.dt_date < a.dt_date
GROUP BY 1,2
ORDER BY 1,2 desc;