Leetcode 1225 Report Contiguous Dates Solution
941
Select status period_state, min(dates) 'start date', max(dates) 'end date' from
(
Select dates, status, (row_number() over (order by dates) - row_number() over (partition by status order by dates)) rn2 from
(
Select fail_date as dates,'missing' as status from failed
union
select *, 'present' from succeeded
order by dates
) tmp
order by dates ) tmp1
where dates >= '2019-01-01'
group by rn2;
Logic:
The difference between Row_number based on dates, row_number based on status and dates would define the groups with same status over a period of time, which can by used for group by clause and find the start and end date.
Comments (2)