Guided Drafts queries

query.sql

-- From Pairing with Renee
select users.id, count(*)
from users
join courses on users.id = courses.user_id
join guided_drafts on courses.id = guided_drafts.course_id
join guided_draft_student_states on guided_drafts.id = guided_draft_student_states.guided_draft_id
where users.school_id in (79158, 79118, 78188, 78241, 78636, 78196, 78814, 79172, 162744, 78424, 78510, 165141, 79173, 169401, 78441, 165142, 78794, 78784, 165139, 136536, 78797, 78082, 78035, 78190, 78238, 159049, 165140, 169378, 78483, 78582, 165144, 78692, 78933, 79026, 165145, 79077, 79082)
and guided_draft_student_states.state in ("submitted", "graded")
and guided_draft_student_states.submitted_at > '2019/01/01' and guided_draft_student_states.submitted_at < '2019/05/01'
group by users.id

-- Or based on the periscope one
select guided_drafts.id,
     schools.district_id as district_id,
     users.school_id as school_id,
     courses.user_id as teacher_id,
     guided_drafts.created_at as created_at,
     IF(guided_drafts.created_at between premium_licenses.starts and premium_licenses.expires, true, false) as premium,
     count(distinct student_states.student_id) as participating_students
from guided_drafts
inner join courses on guided_drafts.course_id = courses.id
-- school_id null are removed users I think
inner join users_no_pii users on courses.user_id = users.id
left join schools_no_pii schools on users.school_id = schools.id
left join premium_licenses on courses.premium_license_id = premium_licenses.id
-- everything but 'unstarted' means the student submitted something to the assignment
left join guided_draft_student_states student_states on guided_drafts.id = student_states.guided_draft_id and
          student_states.state not in ('unstarted')
where schools.id in (79158, 79118, 78188, 78241, 78636, 78196, 78814, 79172, 162744, 78424, 78510, 165141, 79173, 169401, 78441, 165142, 78794, 78784, 165139, 136536, 78797, 78082, 78035, 78190, 78238, 159049, 165140, 169378, 78483, 78582, 165144, 78692, 78933, 79026, 165145, 79077, 79082)
group by guided_drafts.id, district_id, users.school_id, teacher_id, guided_drafts.created_at, premium
order by guided_drafts.id asc

https://noredink.slack.com/archives/C78FMGE91/p1555695517020700