Find maximum number of days between consecutive events-CodePudding

I am trying to find the maximum number of days between any consecutive events for each company. I have a table events with fields company,eventid,date.

|eventid|company |date|
|1 | Company1 |2020-10-15|
|2 | Company2 |2018-03-22|
|3 | Company2 |2019-12-02|
|4 | Company3 |2021-01-02|
|5 | Company3 |2019-06-20|
|6 | Company1 |2018-07-21|
|7 | Company2 |2016-10-18|
|8 | Company2 |2017-04-12|
|9 | Company1 |2020-05-07|
|10| Company3 |2021-11-03|

I have managed to get a column of amount of days between each consecutive event:

select e1.company, e1.date, (e1.date - min(e2.date)) as daysbetween 
from events e1 join events e2 on (e1.company=e2.company and e2.date > e1.date)
group by e1.company,e1.date;

This returns 10 results, but I only need the maximum from the daysbetween column for each company which would give 3 results. However since I used min() to get the daysbetween column I cannot use max() again on that column to find the maximum for each company.

I have been stuck on this for a few days now and cannot work out how I can find the maximum number of days between consecutive events for each company.

CodePudding user response：

You can use LEAD function. I am putting PosgreSQL example, since I noticed you tagged PostgreSQL.

Here is CTE broken into steps to make it clear how it works:

;with cte1 as (
    select 
        company, eventDate, LEAD(eventDate, 1) OVER (PARTITION BY company ORDER BY eventDate) as nextEventDate
    from tbl
    order by company, eventDate
),
cte2 as (
    select 
        company, nextEventDate - eventDate as daysBetweenEvents
    from cte1
),
cte3 as (
    select company, max(daysBetweenEvents)
    from cte2
    group by company
)
select *
from cte3

CodePudding user response：

You can find the number of days between events for each company using lag function through a subquery, and then find the maximum number of days for each company in the main query.

Select Company, Max(daysbetween)
From
(Select Company, date - Lag(date) Over (Partition by Company Order by date) As daysbetween
From events) As T
Group by Company