Curving Grades 2

You're a backend engineer working on an online education platform similar to Khan Academy, called "Con Academy". You're still writing the software that grades on a curve that we started in Curving Grades 1. The grades are in the grades table, and classes are in the classes table.

Table Name:

classes

`column_name`	`type`	`reference`
id	VARCHAR	NULL
subject	VARCHAR	NULL
name	VARCHAR	NULL
semester	VARCHAR	NULL
teacher_id	VARCHAR	teachers.id

Table Name:

grades

`column_name`	`type`	`reference`
score	NUMERIC	NULL
student_id	VARCHAR	students.id
class_id	VARCHAR	classes.id

We are using percentiles to assign letter grades based on the following ranges:

Grade	Range
A	100 - 80%
B	80 - 50%
C	50 - 25%
D	25 - 10%
F	10 - 0%

Now that we know about PARTITION BY, we can write a comprehensive query. For all classes, select each student's numerical score, student_id, the class name from classes.name (NOT class_id), and letter_grade based on their percentile within each class. Order the grades by class name alphabetically, then by highest score. If 2 students in the same class have the same exact score, order the lower student_ids first.

Next
Aggregating Windows

Try running a query…

	`score` `NUMERIC`	`student_id` `VARCHAR`	`class_name` `VARCHAR`	`letter_grade` `TEXT`
1	74.25	74726042	20th Century United States	A
2	68.28	31284132	20th Century United States	A
3	44.2	33527096	20th Century United States	B
4	43.22	35541494	20th Century United States	B
5	40.03	61450500	20th Century United States	B
6	39.84	33578328	20th Century United States	C
7	37.85	43578854	20th Century United States	C
8	35.92	88267756	20th Century United States	C
9	31.57	80768124	20th Century United States	D
10	26.06	89693322	20th Century United States	F
11	68.76	58106664	Applied Circular Economics	A
12	61.57	24606561	Applied Circular Economics	A
13	48.09	26774764	Applied Circular Economics	A
14	46.82	33527096	Applied Circular Economics	B
15	45.59	15047676	Applied Circular Economics	B
16	44.08	29844712	Applied Circular Economics	B
17	34.35	33203344	Applied Circular Economics	C
18	34.32	48383752	Applied Circular Economics	C
19	31.98	56241147	Applied Circular Economics	C
20	29.12	59649105	Applied Circular Economics	D
21	20.5	36022065	Applied Circular Economics	F
22	75.13	59978675	Behavioral Economics	A
23	50.12	58106664	Behavioral Economics	A
24	42.86	85172251	Behavioral Economics	B
25	38.25	46308783	Behavioral Economics	B
26	35.04	17177749	Behavioral Economics	C
27	31.85	40961638	Behavioral Economics	C
28	25.76	53002268	Behavioral Economics	D
29	15.66	68663671	Behavioral Economics	D
30	83.71	33578328	Byzantine Civilization	A
31	70.42	17296531	Byzantine Civilization	A
32	68.04	33939363	Byzantine Civilization	A
33	66.59	89424567	Byzantine Civilization	B
34	59.21	99619751	Byzantine Civilization	B
35	47.3	36357847	Byzantine Civilization	B
36	44.53	20907221	Byzantine Civilization	C
37	40.47	17177749	Byzantine Civilization	C
38	28.99	45132628	Byzantine Civilization	C
39	26.28	62181930	Byzantine Civilization	D
40	23.7	89728030	Byzantine Civilization	D
41	0	94267115	Byzantine Civilization	F
42	92.64	43557314	Calculus	A
43	72.19	43578854	Calculus	A
44	68.65	17177749	Calculus	B
45	62.51	12437547	Calculus	B
46	58.6	93894685	Calculus	C
47	51.37	73412266	Calculus	C
48	50.12	15047676	Calculus	D
49	46.95	68141718	Calculus	D
50	74.24	75936043	Cell Biology	A
51	66.74	55106019	Cell Biology	A
52	59.71	15047676	Cell Biology	A
53	57.36	91755983	Cell Biology	B
54	52.06	88267756	Cell Biology	B
55	43.83	43578854	Cell Biology	B
56	38.92	73923751	Cell Biology	C
57	35.75	63317043	Cell Biology	C
58	33.8	17286425	Cell Biology	C
59	33.48	74916258	Cell Biology	D
60	28.5	84705733	Cell Biology	D
61	12.82	91867583	Cell Biology	F
62	62.82	61450500	Cybersecurity	A
63	62.27	48807452	Cybersecurity	A
64	61.65	72014871	Cybersecurity	A
65	49.58	53463534	Cybersecurity	B
66	49.42	30059687	Cybersecurity	B
67	45.91	63317043	Cybersecurity	B
68	40.84	28598619	Cybersecurity	C
69	38.77	75936043	Cybersecurity	C
70	35.92	47719222	Cybersecurity	C
71	12.82	69062413	Cybersecurity	D
72	0	12437547	Cybersecurity	F
73	100	54568478	Discrete Mathematics	A
74	92.07	24606561	Discrete Mathematics	A
75	89.74	23581505	Discrete Mathematics	A
76	81.95	88267756	Discrete Mathematics	B
77	80.35	12437547	Discrete Mathematics	B
78	79.81	72014871	Discrete Mathematics	B
79	76.94	74916258	Discrete Mathematics	C
80	76.2	86516890	Discrete Mathematics	C
81	74.44	59649105	Discrete Mathematics	C
82	64.17	17286425	Discrete Mathematics	D
83	45.11	85370880	Discrete Mathematics	F
84	100	24606561	Environmental Chemistry	A
85	77.88	36360979	Environmental Chemistry	A
86	77.52	74993337	Environmental Chemistry	B
87	72.82	73923751	Environmental Chemistry	B
88	71.52	91755983	Environmental Chemistry	B
89	68.74	54568478	Environmental Chemistry	C
90	64.73	80768124	Environmental Chemistry	C
91	62.16	80137323	Environmental Chemistry	C
92	54.89	68084872	Environmental Chemistry	D
93	49.33	17296531	Environmental Chemistry	F
94	93.7	36360979	Formal Logic	A
95	86.38	53002268	Formal Logic	A
96	79.82	52343395	Formal Logic	A
97	77.15	47719222	Formal Logic	B
98	73.74	48383752	Formal Logic	B
99	71.9	74726042	Formal Logic	B
100	69.71	15047676	Formal Logic	C
101	65.48	12437547	Formal Logic	C
102	63.39	69062413	Formal Logic	C
103	58.22	37814168	Formal Logic	D
104	50.97	67230862	Formal Logic	D
105	41.15	29844712	Formal Logic	F
106	80.6	33939363	Genetics	A
107	78.64	37379657	Genetics	A
108	77.92	13877406	Genetics	A
109	76.51	40961638	Genetics	B
110	73.72	12437547	Genetics	B
111	73.14	68141718	Genetics	B
112	68.1	74726042	Genetics	C
113	67.44	73922575	Genetics	C
114	67.17	99619751	Genetics	C
115	59.53	66523058	Genetics	D
116	52.2	74916258	Genetics	D
117	33.19	43578854	Genetics	F
118	77.54	43578854	Geometry	A
119	69.32	83063179	Geometry	A
120	67.13	46814334	Geometry	A
121	59.52	36360979	Geometry	B
122	58.94	74993337	Geometry	B
123	57.37	17793075	Geometry	B
124	55.71	89424567	Geometry	C
125	42.58	36022065	Geometry	C
126	36.99	85370880	Geometry	C
127	31.24	57315600	Geometry	D
128	22.5	84705733	Geometry	D
129	21.47	47719222	Geometry	F
130	81.6	74726042	Immunology	A
131	81.08	72014871	Immunology	A
132	72.53	46814334	Immunology	B
133	68.19	22254032	Immunology	B
134	51.29	20907221	Immunology	C
135	48.23	17177749	Immunology	C
136	41.72	10162534	Immunology	D
137	40.7	57315600	Immunology	D
138	67.89	85370880	Inorganic Chemistry	A
139	52.39	33939363	Inorganic Chemistry	A
140	52.31	80137323	Inorganic Chemistry	B
141	38.94	34828328	Inorganic Chemistry	B
142	38.73	37814168	Inorganic Chemistry	B
143	23.31	91867583	Inorganic Chemistry	C
144	18.71	81865678	Inorganic Chemistry	C
145	9.36	46814334	Inorganic Chemistry	D
146	8.85	83063179	Inorganic Chemistry	D
147	65.18	12437547	Linear Algebra	A
148	60.32	13877406	Linear Algebra	A
149	57.28	43557314	Linear Algebra	A
150	47.75	85172251	Linear Algebra	B
151	45.85	81865678	Linear Algebra	B
152	44.31	53463534	Linear Algebra	B
153	42.21	45132628	Linear Algebra	C
154	37.28	48383752	Linear Algebra	C
155	34.33	17793075	Linear Algebra	C
156	27.2	67230862	Linear Algebra	D
157	22	33203344	Linear Algebra	F
158	77.79	83063179	Neurobiology	A
159	72.79	21125533	Neurobiology	A
160	72.02	52379077	Neurobiology	A
161	67.98	22254032	Neurobiology	B
162	56.12	31284132	Neurobiology	B
163	38.16	98182918	Neurobiology	B
164	35.27	17177749	Neurobiology	C
165	34.91	13241753	Neurobiology	C
166	34.67	37814168	Neurobiology	C
167	19.78	85172251	Neurobiology	D
168	13.21	45132628	Neurobiology	F
169	92.22	59978675	Number Theory	A
170	84.16	53002268	Number Theory	A
171	82.96	24699645	Number Theory	B
172	66.79	48383752	Number Theory	B
173	59.59	83063179	Number Theory	B
174	57.61	37814168	Number Theory	C
175	41.19	36022065	Number Theory	C
176	38.23	26774764	Number Theory	C
177	37.77	17296531	Number Theory	D
178	35.3	52379077	Number Theory	F
179	100	72529150	Postwar Germany	A
180	85.77	72014871	Postwar Germany	A
181	83.93	74916258	Postwar Germany	A
182	81.46	52444233	Postwar Germany	B
183	75.49	33578328	Postwar Germany	B
184	74.67	58106664	Postwar Germany	B
185	68.79	33527096	Postwar Germany	C
186	65.57	66523058	Postwar Germany	C
187	60.85	68663671	Postwar Germany	C
188	56.94	36022065	Postwar Germany	D
189	50.25	47719222	Postwar Germany	D
190	45.49	24606561	Postwar Germany	F
191	78.76	36022065	The Ottoman Empire	A
192	74.6	73923751	The Ottoman Empire	A
193	71.11	69062413	The Ottoman Empire	A
194	61.06	30059687	The Ottoman Empire	B
195	59.7	37814168	The Ottoman Empire	B
196	59.64	72529150	The Ottoman Empire	B
197	53.84	84705733	The Ottoman Empire	C
198	43.6	48383752	The Ottoman Empire	C
199	28.39	66523058	The Ottoman Empire	C
200	26.07	13877406	The Ottoman Empire	D
201	19.63	10162534	The Ottoman Empire	F
202	50.35	52343395	Trigonometry	A
203	45.01	71067865	Trigonometry	A
204	41.89	56241147	Trigonometry	A
205	38.83	48383752	Trigonometry	B
206	38.38	59649105	Trigonometry	B
207	37.74	28319702	Trigonometry	B
208	35.8	12437547	Trigonometry	C
209	30.84	17322083	Trigonometry	C
210	22.73	26774764	Trigonometry	C
211	21.15	68678766	Trigonometry	D
212	15.29	36022065	Trigonometry	D
213	9.23	91755983	Trigonometry	F
213 rows

You haven't solved this challenge yet!
Are you sure you want to reveal the answer?

WITH percentile_scores AS (
  SELECT
    *,
    cume_dist() OVER (
      PARTITION BY class_id
      ORDER BY score
    ) * 100 AS percentile
  FROM grades
  ORDER BY score DESC
)
SELECT
  score,
  student_id,
  classes.name AS class_name,
  CASE
    WHEN percentile > 80 THEN 'A'
    WHEN percentile > 50 THEN 'B'
    WHEN percentile > 25 THEN 'C'
    WHEN percentile > 10 THEN 'D'
    ELSE 'F'
  END AS letter_grade
FROM percentile_scores
JOIN classes
  ON classes.id = percentile_scores.class_id
ORDER BY classes.name, score DESC, student_id;