Queries for Prereq analysis

Advanced Missions - Completion

SELECT alternative, mission_title, INTEGER(ROUND(NTH(500, QUANTILES(percent_mastered, 1001)) * 100)) as median_pct_mastered,
INTEGER(ROUND(NTH(500, QUANTILES(ud_total_problem_seconds, 1001)) / 60)) as median_problem_minutes,
NTH(500, QUANTILES(ud_age, 1001)) as median_age,
COUNT(1) as N FROM
(SELECT ud_age, ud_total_problem_seconds, mission_title, percent_mastered, alternative FROM [eli.advanced_mission_demos])
GROUP BY alternative, mission_title
order by mission_title, alternative

Happiness by mission and condition

SELECT
       bm.alternative as alternative, 
       ud.active_mission as mission,
       AVG(r.response) as response,
       COUNT(1) as num_responses
       
FROM [2014_05_24_derived.user_segments] ud
          JOIN EACH
 (SELECT bingo_id, alternative
             FROM [bingo.2014_05_26_bingo_map]
             WHERE canonical_name='Athena__Prerequisites_in_Advanced_Missions') AS bm
            ON bm.bingo_id=ud.gae_bingo_identity
JOIN EACH
(SELECT MAX(response) as response, user_key.name FROM [2014_05_24.SurveyResponse] group by user_key.name) r
on r.user_key.name = ud.key_name
GROUP BY alternative, mission
order by alternative, mission

In-mission learning delta

(select ma.mission as mission, ud.alternative as alternative, avg(ma.delta) as avg_delta, count(1) as N, stddev(ma.delta)  / sqrt(count(1)) as stderr
FROM
[eli.advanced_mission_demos] ud
JOIN EACH
(SELECT mission, user_id,  delta from eli.mission_analytics) ma
on ma.mission = ud.mission_slug and ma.user_id = ud.ud_user_id
group by mission, alternative
order by mission, alternative

Relevant Users

SELECT ud.*, mt.slug as mission_slug, mt.title as mission_title, mm.percent_mastered as percent_mastered, bm.alternative as alternative
FROM (SELECT * FROM [2014_05_24_derived.user_segments]) ud
JOIN EACH (SELECT slug, title FROM eli.mission_title_slug) mt
ON ud.active_mission = mt.title
JOIN EACH (SELECT bingo_id, alternative
             FROM [bingo.2014_05_26_bingo_map]
             WHERE canonical_name='Athena__Prerequisites_in_Advanced_Missions') AS bm
ON bm.bingo_id=ud.gae_bingo_identity
JOIN EACH
(SELECT percent_mastered, user_email, mission  FROM [2014_05_24_derived.mission_mastery]
where mission is not null and mission in(
    'algebra2',
    'geometry',
    'trigonometry',
    'probability',
    'differential-calculus')) mm
on ud.user_email = mm.user_email and mt.slug = mm.mission;