/*
EY SQL Interview Question  

You have a table named ATTENDANCE that records employees' daily attendance, indicating whether they were absent or present on each day
The table structure is as follows:

| Column Name  | Data Type |
|--------------|-----------|
| `EMPLOYEE`   | VARCHAR   |
| `DATES`      | INT       |
| `STATUS`     | VARCHAR   |

Task:  

Your task is to write an SQL query to generate a report that shows the period of presence and absence for each employee.
The output should be in the following format:

Expected Output:

| EMPLOYEE | FROM_DATE  | TO_DATE    | STATUS  |
|----------|---------- -|------- ----|---------|
| A1       | 2024-01-01 | 2024-01-03 | PRESENT |
| A1       | 2024-01-04 | 2024-01-04 | ABSENT  |
| A1       | 2024-01-05 | 2024-01-06 | PRESENT |
| A1       | 2024-01-07 | 2024-01-09 | ABSENT  |
| A1       | 2024-01-10 | 2024-01-10 | PRESENT |
| A2       | 2024-01-11 | 2024-01-12 | PRESENT |
| A2       | 2024-01-13 | 2024-01-13 | ABSENT  |
| A2       | 2024-01-14 | 2024-01-14 | PRESENT |
| A2       | 2024-01-15 | 2024-01-15 | ABSENT  |

*/

CREATE TABLE ATTENDANCE (EMPLOYEE VARCHAR(10), DATES DATE, STATUS VARCHAR (20));

INSERT INTO ATTENDANCE VALUES('A1', '2024-01-01','PRESENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-02','PRESENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-03','PRESENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-04','ABSENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-05','PRESENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-06','PRESENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-07','ABSENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-08','ABSENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-09','ABSENT');
INSERT INTO ATTENDANCE VALUES('A1', '2024-01-10','PRESENT');
INSERT INTO ATTENDANCE VALUES('A2', '2024-01-11','PRESENT');
INSERT INTO ATTENDANCE VALUES('A2', '2024-01-12','PRESENT');
INSERT INTO ATTENDANCE VALUES('A2', '2024-01-13','ABSENT');
INSERT INTO ATTENDANCE VALUES('A2', '2024-01-14','PRESENT');
INSERT INTO ATTENDANCE VALUES('A2', '2024-01-15','ABSENT');

WITH CTE AS
(SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY DATES) rn,
  DENSE_RANK() OVER(PARTITION BY EMPLOYEE,STATUS ORDER BY DATES) rnk
FROM ATTENDANCE
ORDER BY DATES)
    
, FLAGS_CTE AS 
(SELECT
    EMPLOYEE,
    DATES,
    STATUS,
    rn - rnk AS FLAG
FROM CTE)
    
SELECT
    EMPLOYEE,
    MIN(DATES) AS FROM_DATE,
    MAX(DATES) AS TO_DATE,
    MAX(STATUS) AS STATUS
FROM FLAGS_CTE
GROUP BY EMPLOYEE,FLAG;