A Beautiful SQL Question from My Business Analyst Interview
I was asked this sql question in the interview for the business analyst role.
Table Details:
There is a table named attendance with columns id, date, and present.
id: id of that employee
date: date of presence.
present: This column has the value of either 1 or 0, 1 represents present, and 0 represents absent.
Now the question is to write a SQL query to find out the employee ID who came to the office for most consecutive days.
Here is the sample table.
Step 1: we will filter those dates where the employee was present. means present=1. In CTE x you can find what is discussed in step 1.
Step 2: we will generate a row number for each id order by date. Then, subtract that row number from the date column. The logic is if there are consecutive days, then after subtracting the row number from the date, we will get a fixed value of the date named grouped.
In CTE y you can find what is discussed in step 2.
Step 3: Now we will count partition by id and grouped. After that, we will generate a rank based on their count. We will take that id where rank=1
In CTE z and CTE a you can find what is discussed in step 3.
Here is the final Query and it’s result.
I hope you understand theapproach.
Thank You,
