EY SQL Interview Question Solved Step-by-Step

[@user-gq6cg3ls7f]

with cte as( select *, DATEADD(DAY, -1*ROW_NUMBER() over (partition by employee, status order by dates), DATES) FLAG from ATTENDANCE_TUE ), cte2 as( select *, FIRST_VALUE(DATES) over (partition by employee, FLAG order by DATES) start_date, FIRST_VALUE(DATES) over (partition by employee, FLAG order by DATES DESC) end_date from cte ) select distinct employee,start_date , end_date, status from cte2

[@ishamajumdar5580]

My Approach : WITH cte AS ( SELECT *, DAY(dates) - ROW_NUMBER() OVER(PARTITION BY employee, status ORDER BY Dates) AS diff FROM attendance ORDER BY dates ) SELECT employee, MIN(dates) AS From_Date, MAX(dates) AS To_Date, status FROM cte GROUP BY employee, status, diff;