Longest Streak calculation in SQL
Longest Streak calculation in SQL is being asked now a days to check the window function logic of the candidate. This question might get a bit difficult to crack if you have not practised it before hand.
So, lets try to solve Longest Streak calculation in SQL in very simple way
Problem Statement – I work in a Tech Support domain and we have a tracker where we calculate the number of incidents per day. So the table is a simple 2 columns i.e. Date and Number of incidents
Desired Output – What is the longest time period without any incident ?
So the output of the table below would be-
9th Jan’21 – 6
Arrange the table in the order of dates.
Create one more column using Lead function with date, so the new column will have the next date.
In the last column create a difference of date tagged
Take the first row when you order it in descending order on difference column
Can you write the query with the following approach?
Add your answer in this link for evaluation – https://thedatamonk.com/question/longest-streak-in-sql/