Rolling period repeat incidence - postgresql-CodePudding

I have the dataset:

I need to see whether there are tickets in a month with the same ticket type that were closed during the previous 3 months.

For example, if I check the Sept closed tickets. 3 months from September, incl September are Jul, Aug, Sept. There were 2 tickets in Sept with ticket type 3 and ticket type 4. During Jul, Aug, and Sept, there were indeed tickets with ticket type 3 and 4. Hence, the repeat # of tickets is 2.

Now, let's look at August. 3 months from August, inc August are Jun, Jul, and Aug. There was 1 ticket closed in August with ticket type 1. If we check tickets closed in June, Jul and Aug, there are indeed tickets with type 1. Hence, the repeat # of tickets is 1.

If there were no tickets with the same ticket type, the repeat # of tickets should be equal to 0.

I assume I need to look into the window functions, don't I?

I found the article at https://chartio.com/learn/postgresql/how-to-create-a-rolling-period-running-total/ and I feel it is a direction to go. Is it correct?

CodePudding user response：

You can do something like this:

with cte as (
    select distinct
        date_part('month', t.closed_date) as month,
        t.type
    from test as t
)
select
    c.month,
    count(distinct c.type) as repeat_cnt
from cte as c
    inner join cte as c2 on
        c2.type = c.type and
        c2.month between c.month - 3 and c.month - 1
group by
    c.month;

the output is:

7,2 -- July - 2 tickets
8,1 -- August - 1 ticket
9,2 -- September - 2 tickets

db fiddle example