/*
EY SQL Interview Question  

You are provided with a table named `Students` that contains information about students' scores and attendance.
The table structure is as follows:  

| Column Name  | Data Type |
|--------------|-----------|
| `Name`       | VARCHAR   |
| `Score`      | INT       |
| `Attendance` | INT       |

Task:  

Write an SQL query to divide the students into two groups based on their scores

For each group, determine whether a student passes or fails based on the following conditions:  
- First Half: A student passes if their attendance is greater than 70; otherwise, they fail.  
- Second Half: A student passes if their attendance is greater than 75; otherwise, they fail.  

Expected Output:  

| Name    | Score | Attendance | Result |
|---------|-------|------------|--------|
| Alice   | 98    | 66         | Fail   |
| Bob     | 86    | 56         | Fail   |
| Charlie | 85    | 99         | Pass   |
| David   | 71    | 100        | Pass   |
| Frank   | 82    | 66         | Fail   |
| Harry   | 92    | 99         | Pass   |
| Phil    | 49    | 74         | Fail   |
| Rodri   | 99    | 96         | Pass   |
| Vini    | 40    | 40         | Fail   |
*/

CREATE TABLE Student
( 
 Name varchar(10),
 Score INT,
 Attendance INT
);

INSERT INTO Student
VALUES
('Alice',98,66),
('Bob',86,56),
('Charlie',85,99),
('David',71,100),
('Frank',82,66),
('Harry',92,99),
('Vini',40,40),
('Rodri',99,96),
('Phil',49,74);


WITH flags AS
(SELECT *,NTILE(2) OVER(ORDER BY Score Desc) AS flag FROM Student)

SELECT
    Name,
    Score,
    Attendance,
    CASE
     WHEN flag = 1 AND Attendance > 70 THEN 'Pass'
     WHEN flag = 2 AND Attendance > 75 THEN 'Pass'
     ELSE 'Fail'
    END AS Result
FROM flags
ORDER BY Name;