Test Scores

You are working in the admissions department at a university, tasked with analyzing the standardized test scores (SAT and ACT) of the applicants. The applicants' test scores are in sat_scores and act_scores.

Table Name:

sat_scores

`column_name`	`type`
id	VARCHAR
student_id	VARCHAR
math_score	INTEGER
reading_score	INTEGER

Table Name:

act_scores

`column_name`	`type`
id	VARCHAR
student_id	VARCHAR
math_score	INTEGER
english_score	INTEGER
reading_score	INTEGER
science_score	INTEGER

The total SAT score is out of 1600 possible points consisting of the sum of two 800-point sections. The total ACT score is out of 36 possible points consisting of the average of four 36-point sections. Each of the sections is a separate column in their respective tables.

Between the 2 exams, their score range is totally different. Some applicants have only taken the SAT, and some have only taken the ACT. We need a way to combine the scores and compare across exams, i.e. did some who got 29/36 on the ACT do better than someone who got 1300/1600 on the SAT? For that we'll use a normalized score call the z-score.

The z-score is a scale-independent way to represent the values in a distribution. It is like the concept of "grading on a curve," where everyone's grade is determined in comparison to the population instead of their absolute number. To convert each score to its corresponding z-score, first you need the average and the standard deviation of the population's scores. Each z-score (z) is calculated by subtracting the average (µ) from the raw score (x) and then dividing by the standard deviation (σ).

For example, if the SAT has an average score of 1050 and a standard deviation of 150, someone who with a raw score of 1200 has a z-score equal to (1200 - 1050) / 150 which is 1.33.

For this challenge, convert both the SAT and ACT scores to their corresponding z-scores, then UNION them together into a single table, making sure the following columns are present:

id - The ID of the test result.
student_id - The ID of the student who took the test.
raw_score - The overall test result score.
normalized_score - The z-score.
test - Whether the result is for the SAT or ACT. This value should be either the string 'SAT' or 'ACT'.

Don't worry about computing the standard deviation from scratch. There are functions to help you out!

Next
Correlated Subqueries

Try running a query…

	`id` `VARCHAR`	`student_id` `VARCHAR`	`raw_score` `NUMERIC`	`normalized_score` `NUMERIC`	`test` `TEXT`
1	848868805707	35122540	1077	0.06461738556182424434	SAT
2	906216679690	32552392	1057	-0.03913447294589355629	SAT
3	787970111819	27914877	1030	-0.17919948193131258714	SAT
4	610200156263	73142864	1035	-0.15326151730438313698	SAT
5	126035333235	54114818	1285	1.1436367140420894	SAT
6	555583397894	59861998	861	-1.0559026863215280	SAT
7	519817476867	48565608	1018	-0.24145059703594326752	SAT
8	608286286516	76961414	839	-1.1700297306800176	SAT
9	120565520504	76961414	971	-0.48526746452908009900	SAT
10	370838352677	46680638	983	-0.42301634942444941862	SAT
11	756583305211	17793075	791	-1.4190341910985403	SAT
12	356407107340	17793075	997	-0.35039004846904695818	SAT
13	892869113326	56978562	1231	0.86350669607125130919	SAT
14	631473303268	68972803	961	-0.53714339378293899931	SAT
15	249700299634	16929842	1386	1.6675835995060643	SAT
16	689561554388	45710678	1216	0.78569280219046295872	SAT
17	613950022412	75176496	782	-1.4657225274270133	SAT
18	867722480690	33591744	711	-1.8340416251294115	SAT
19	471575922965	56387444	1209	0.74937965171276172850	SAT
20	162582193175	56387444	1125	0.31362184598034696586	SAT
21	493639491805	70763093	1171	0.55225112054809790730	SAT
22	415600425183	70763093	751	-1.6265379081139759	SAT
23	457832801973	70763093	1377	1.6208952631775913	SAT
24	880745216954	75844246	1012	-0.27257615458825860770	SAT
25	969332278559	99645022	1136	0.37068536815959175620	SAT
26	363609797215	99645022	1160	0.49518759836885311696	SAT
27	300596878599	95517523	971	-0.48526746452908009900	SAT
28	890066306978	43395712	693	-1.9274182977863575	SAT
29	301487151734	75062775	1020	-0.23107541118517148745	SAT
30	923751849890	93604380	1289	1.1643870857436329	SAT
31	357587876427	96263762	1258	1.0035717050566703	SAT
32	230581418719	42033441	1291	1.1747622715944047	SAT
33	732391433108	13987471	1101	0.18911961577108560510	SAT
34	912472596295	19072210	1147	0.42774889033883654655	SAT
35	534345851162	33684394	1144	0.41218611156267887645	SAT
36	670280996562	62181930	1289	1.1643870857436329	SAT
37	388035304654	42026004	739	-1.6887890232186066	SAT
38	436843619702	73630878	1370	1.5845821126998900	SAT
39	631142440901	47753751	1138	0.38106055401036353626	SAT
40	250695051032	23882677	1013	-0.26738856166287271767	SAT
41	848720385706	23882677	1008	-0.29332652628980216783	SAT
42	412266006960	23882677	1369	1.5793945197745041	SAT
43	841320460870	66985108	818	-1.2789691821131213	SAT
44	708806557279	15954810	1126	0.31880943890573285589	SAT
45	886756940283	76645896	1172	0.55743871347348379734	SAT
46	654149471517	76645896	1231	0.86350669607125130919	SAT
47	329045710351	97207747	868	-1.0195895358438268	SAT
48	906241303792	36042560	616	-2.3268629530410711	SAT
49	206019247937	39493249	863	-1.0455275004707562	SAT
50	157335763341	64910557	886	-0.92621286318688075167	SAT
51	814334723990	63127936	1224	0.82719354559355007897	SAT
52	751748385175	66030952	1131	0.34474740353266230604	SAT
53	468596212873	66030952	1114	0.25655832380110217551	SAT
54	864250498651	66030952	1101	0.18911961577108560510	SAT
55	271845931918	84771922	988	-0.39707838479751996846	SAT
56	561085400653	26406627	1389	1.6831463782822219	SAT
57	758356421348	39718656	1140	0.39143573986113531633	SAT
58	593491153183	73183063	31.2500000000000000	1.97975310719497195450258252640446	ACT
59	348239338383	73183063	24.7500000000000000	0.45911556547948389491418826683235	ACT
60	275145050014	27914877	21.2500000000000000	-0.35968926467500967563340864216802	ACT
61	230862735924	80344355	14.7500000000000000	-1.88032680639049773522180290174013	ACT
62	664863317043	59861998	24.0000000000000000	0.28365738758923527265398892918941	ACT
63	854403858498	46680638	24.2500000000000000	0.34214344688598481340738870840372	ACT
64	832474993337	56978562	28.2500000000000000	1.27792039563397746546178517583271	ACT
65	323999869175	25973878	26.0000000000000000	0.75154586196323159868118716290391	ACT
66	681003877406	25973878	21.7500000000000000	-0.24271714608151059412660908373939	ACT
67	115015151462	99645022	24.0000000000000000	0.28365738758923527265398892918941	ACT
68	242935132628	95517523	21.0000000000000000	-0.41817532397175921638680842138233	ACT
69	643069080324	42033441	16.0000000000000000	-1.58789650990675003145480400566857	ACT
70	148529426711	42033441	20.5000000000000000	-0.53514744256525829789360797981095	ACT
71	124375476682	13987471	26.0000000000000000	0.75154586196323159868118716290391	ACT
72	379142873955	33684394	24.7500000000000000	0.45911556547948389491418826683235	ACT
73	880610137949	42026004	18.5000000000000000	-1.00303591693925462392080621352545	ACT
74	313300902220	15954810	24.0000000000000000	0.28365738758923527265398892918941	ACT
75	413647052914	36042560	28.0000000000000000	1.21943433633722792470838539661840	ACT
76	732407436332	64910557	17.0000000000000000	-1.35395227271975186844120488881132	ACT
77	108027473911	54568478	19.7500000000000000	-0.71060562045550692015380731745389	ACT
77 rows

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

WITH total_sat_scores AS (
  SELECT
    *,
    math_score + reading_score AS score
  FROM sat_scores
),
total_act_scores AS (
  SELECT
    *,
    (math_score + english_score + reading_score + science_score) / 4.0 AS score
  FROM act_scores
)
SELECT
  id,
  student_id,
  score AS raw_score,
  (score - (SELECT avg(score) FROM total_sat_scores)) /
    (SELECT stddev(score) FROM total_sat_scores) AS normalized_score,
  'SAT' AS test
FROM total_sat_scores
UNION ALL
SELECT
  id,
  student_id,
  score AS raw_score,
  (score - (SELECT avg(score) FROM total_act_scores)) /
    (SELECT stddev(score) FROM total_act_scores) AS normalized_score,
  'ACT' AS test
FROM total_act_scores;