SELECT
  sat.NAME_LAST,
  sat.NAME_FIRST,
  MIN(sat.SECONDARY_ID) AS SECONDARY_ID,
  MAX(sat.MAX_SAT_EBRW) AS MAX_SAT_EBRW,
  MAX(sat.MAX_SAT_MATH) AS MAX_SAT_MATH,
  MAX(sat.SAT_SUPER_SCORE) AS SAT_SUPER_SCORE,
  MAX(psat.MAX_PSAT_EBRW) AS MAX_PSAT_EBRW,
  MAX(psat.MAX_PSAT_MATH) AS MAX_PSAT_MATH,
  MAX(psat.PSAT_SUPER_SCORE) AS PSAT_SUPER_SCORE,
  -- See score cutoffs by percentage below
  (CASE WHEN (MAX(sat.MAX_SAT_EBRW) > 550 OR MAX(psat.MAX_PSAT_EBRW) >550) THEN TRUE END) AS AP_ArtHist_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1060 OR MAX(psat.PSAT_SUPER_SCORE) >1060) THEN TRUE END) AS AP_Bio_Potential,--60
(CASE WHEN (MAX(sat.MAX_SAT_MATH) > 600 OR MAX(psat.MAX_PSAT_MATH) > 600) THEN TRUE END) AS AP_CalcAB_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) >  1180 OR MAX(psat.PSAT_SUPER_SCORE) > 1180) THEN TRUE END) AS AP_CSA_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) >  1060 OR MAX(psat.PSAT_SUPER_SCORE) > 1060) THEN TRUE END) AS AP_CSPrinciples_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1120 OR MAX(psat.PSAT_SUPER_SCORE) > 1120) THEN TRUE END) AS AP_EnvSci_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1090 OR MAX(psat.PSAT_SUPER_SCORE) > 1090) THEN TRUE END) AS AP_EuroHist_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1200 OR MAX(psat.PSAT_SUPER_SCORE) > 1200) THEN TRUE END) AS AP_Macroecon_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1150 OR MAX(psat.PSAT_SUPER_SCORE) > 1150) THEN TRUE END) AS AP_Microecon_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1020 OR MAX(psat.PSAT_SUPER_SCORE) > 1020) THEN TRUE END) AS AP_Seminar_Potential,--80
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1170 OR MAX(psat.PSAT_SUPER_SCORE) > 1170) THEN TRUE END) AS AP_Stats_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 1100 OR MAX(psat.PSAT_SUPER_SCORE) > 1100) THEN TRUE END) AS AP_USHist_Potential,--60
(CASE WHEN (MAX(sat.SAT_SUPER_SCORE) > 990 OR MAX(psat.PSAT_SUPER_SCORE) > 990) THEN TRUE END) AS AP_WorldHist_Potential--60
FROM `sat_scores.SAT_MAX_SCORE_vw` AS sat
FULL JOIN `psat_scores.PSAT_MAX_SCORE_vw` AS psat
ON sat.SECONDARY_ID = psat.SECONDARY_ID

WHERE sat.NAME_LAST IS NOT NULL

GROUP BY NAME_LAST, NAME_FIRST

ORDER BY NAME_LAST, NAME_FIRST

-- Class                      Score Source        _90%  _80%    _70%    _60%    _50%    _40%    _30%    _20%    _10%
-- AP Art History           EBRW Section Score  700   630     590     550     520     490     450     410     410
-- AP Biology                 Total Score           1190    1130    1090    1060    1030    1000    970   930     870
-- AP Calculus AB           Math Section Score  700   660   620     600     570     550     520     490     450
-- AP Chemistry             Total Score         1380    1300    1250    1200    1160    1120    1080    1030    950
-- AP Computer Science A    Total Score         1390    1300    1230    1180    1130    1080    1020    960     860
-- AP Computer Science Pr   Total Score         1240    1160    1100    1060    1010    970   930   870     790
-- AP English Language      EBRW Section Score  620   590   560     550     530     510     490   470   430
-- AP English Literature      EBRW Section Score    580   550   520     500     490     470     450     420     390
-- AP Environmental Science Total Score         1260    1200    1150    1120    1080    1050    1020    970     910
-- AP European History      Total Score         1290    1200    1140    1090    1050    1010    960   900   860
-- AP Gov & Pol: Comp         Total Score           1300    1210    1150    1110    1060    1020    970     920     830
-- AP Gov & Pol: US         Total Score         1340    1260    1210    1160    1120    1080    1030    980   900
-- AP Human Geography         Total Score           1230    1150    1090    1050    1000    960   910   860     860
-- AP Macroeconomics          Total Score           1400    1310    1250    1200    1150    1110    1060    1000    910
-- AP Microeconomics          Total Score           1350    1260    1200    1150    1100    1060    1010    950     860
-- AP Music Theory          Total Score         1370    1250    1170    1110    1050    990     920     860     860
-- AP Physics 1             Total Score         1420    1350    1300    1250    1220    1180    1140    1090    1010
-- AP Phys C: Elec. & Mag.  Total Score         1500    1390    1330    1270    1220    1170    1120    1050    950
-- AP Phys C: Mechanics     Total Score         1360    1280    1230    1180    1140    1100    1060    1000    920
-- AP Psychology              Total Score           1230    1150    1090    1050    1010    970   920   870     860
-- AP Seminar                 Total Score           1120    1020    960   900   860     860     860     860     860
-- AP Statistics              Total Score           1330    1260    1210    1170    1140    1100    1060    1010    940
-- AP U.S. History          Total Score         1260    1190    1140    1100    1060    1020    980     930     860
-- AP World Hist: Modern        Total Score         1150    1080    1030    990   960   920     880     830     760