query to find the maximum gap between dates-CodePudding

I have a table with the name of each customer and date columns and want to write a query to give me the number of gap days for each user,

name date
ali 2022-01-01
ali 2022-01-04
ali 2022-01-05
ser 2022-03-01

the answer should be 3 for ali and for ser will be null.

here is what I tried:

select name ,min(date) over (partition by name order by date) start_date , max(date) over (partition by name order by date) end_date from table

CodePudding user response：

my approach is to match every record with the closest date then find the maximum gap and left join with the original table to get the gap for each user.

here's MySQL version:

select
    cu.name, max(cg.gap) maxgap
from
    customers cu left join
    (
    select
        c.name, datediff(min(cn.date), c.date) gap
    from
        customers c left join customers cn on c.name = cn.name
    where
        cn.date > c.date
    group by
        c.name, c.date
    ) cg
    on cu.name = cg.name
group by
    cu.name

CodePudding user response：

One approach to achieve this is using a window function (like lag, lead) to find the prior/next day and then find the difference between the dates (current and prior, for example ) using datediff function. Something like this..

SELECT name,
       MAX(datediff(date, PreviousDate)) AS Gap
FROM (SELECT name,
             date,
             LAG(date) OVER(PARTITION BY name ORDER BY date) as PreviousDate
      FROM table t
GROUP BY name