Register Now

Login

Lost Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Login

Register Now

It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways

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

Longest Streak calculation in SQL

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

Date_of_incidenceTicketsLEAD_rowDifference
1/1/202112/1/20211
2/1/202123/1/20211
3/1/202134/1/20211
4/1/202145/1/20211
5/1/202156/1/20211
6/1/2021679/1/20213
9/1/2021215/1/20216
15/1/2021316/1/20211
16/1/20214NULL

Approach

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/

Important Links for SQL Interview Preparation

How to prepare for SQL in 15 days –

Get your hands on 1400+ Data Science interview Questions spread across 10 e-books, Download the PDF now 🙂

About TheDataMonkGrand Master

I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)

Follow Me

Leave a reply