Quartiles and Distance From the Average

For every student with a known gpa, return first_name, last_name, gpa, quartile (NTILE(4) by gpa descending, ties broken by id, so the top quarter is 1), avg_gpa (the average gpa of all these students, rounded to 2 places and repeated on every row) and diff (this student's gpa minus that average, rounded to 2 places). Sort by gpa descending, then id.

Expected result:

+------------+-----------+------+----------+---------+------+
| first_name | last_name |  gpa | quartile | avg_gpa | diff |
+------------+-----------+------+----------+---------+------+
| Evelyn     | Park      | 3.98 |        1 |    3.25 | 0.73 |
| Sophia     | Rossi     | 3.92 |        1 |    3.25 | 0.67 |
| Grace      | Lee       | 3.88 |        1 |    3.25 | 0.63 |
| Ava        | Patel     |  3.8 |        1 |    3.25 | 0.55 |
| Mia        | Haddad    |  3.8 |        1 |    3.25 | 0.55 |
| Emily      | Zhang     | 3.72 |        1 |    3.25 | 0.47 |
| Charlotte  | Dubois    | 3.67 |        1 |    3.25 | 0.42 |
| Scarlett   | Johnson   |  3.6 |        2 |    3.25 | 0.35 |
| Harper     | Singh     | 3.55 |        2 |    3.25 |  0.3 |
| Zoe        | Campbell  |  3.5 |        2 |    3.25 | 0.25 |
+------------+-----------+------+----------+---------+------+
(showing 10 of 27 rows)

Hints3 available

Topics:

Window Functions

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.