Skip to content

Instantly share code, notes, and snippets.

@esotrope

esotrope/reporting-sql-strategy.sql

Created May 22, 2017 18:39
Show Gist options
  • Save esotrope/ce512e37a4e441a83891b6f6fc256189 to your computer and use it in GitHub Desktop.
Save esotrope/ce512e37a4e441a83891b6f6fc256189 to your computer and use it in GitHub Desktop.
Download ZIP
reporting sql strategy
Raw
reporting-sql-strategy.sql
-- Current path queries by endpoint:
-- /groups/{groupId}/latestassessment
-- gets latest assessment that a group took
select
e.completed_at,
a.id as assessment_id,
a.name as assessment_name,
a.grade_id as assessment_grade_id,
a.type_id as assessment_type_id,
a.subject_id as assessment_subject_id
from exam e
join student_group_membership sgm on e.student_id=sgm.student_id
join asmt a on e.asmt_id=a.id
join school s on e.school_id=s.id
where e.school_year=:school_year
and sgm.student_group_id=:group_id
and (0=:group_subject_id or a.subject_id=:group_subject_id)
and (1=:statewide or s.district_id in (:district_ids) or e.school_id in (:school_ids))
order by e.completed_at desc
limit 1
-- get all exams for latest assessment (security already performed in previous query)
select e.*,
st.id as student_id,
st.last_or_surname as student_last_name,
st.first_name as student_first_name,
st.gender_id as student_gender_id
from exam e
join student_group_membership sgm on e.student_id=sgm.student_id
join student st on e.student_id=st.id
where e.asmt_id=:assessment_id
and sgm.student_group_id=:group_id
-- /groups/{groupId}/assessments
-- gets all assessments of a group to populate the "custom" dropdown when clicked
select a.id,
a.name,
a.grade_id,
a.type_id,
a.subject_id,
a.school_year,
aa.min_score,
aa.max_score,
aa.cut_point_1,
aa.cut_point_2,
aa.cut_point_3
from asmt a
left join asmt_score aa on a.id=aa.asmt_id
where exists ( -- this subquery is redundant with get data already searched in /latestassessment
select e.asmt_id
from exam e
join student_group_membership sgm on e.student_id=sgm.student_id
join school s on e.school_id=s.id
where
e.school_year=:school_year
and sgm.student_group_id=:group_id
and (0=:group_subject_id or a.subject_id=:group_subject_id)
and (1=:statewide or s.district_id in (:district_ids) or e.school_id in (:school_ids))
and a.id = e.asmt_id
)
-- /groups/{groupId}/assessment/{assessmentId}/exams
-- /groups/{groupId}/assessment/{assessmentId}/iabexams
-- get all exams for a given assessment
-- this query is the same as the internal query but with security added
select e.*,
st.id as student_id,
st.last_or_surname as student_last_name,
st.first_name as student_first_name,
st.gender_id as student_gender_id
from exam e
join student_group_membership sgm on e.student_id=sgm.student_id
join student st on e.student_id=st.id
join asmt a on e.asmt_id=a.id
join school s on e.school_id=s.id
where e.asmt_id=:assessment_id
and e.school_year=:school_year
and sgm.student_group_id=:group_id
and (0=:group_subject_id or a.subject_id=:group_subject_id)
and (1=:statewide or s.district_id in (:district_ids) or e.school_id in (:school_ids))
-- Proposed path:
-- /groups/{groupId}/exams gets all assessments taken by a group (this returns the completed_at date time for the client to determine the latest)
-- this will serve as the data to populated the "custom" dropdown and latest assessment
select
e.completed_at,
a.id as assessment_id,
a.label as assessment_label,
a.grade_id as assessment_grade_id,
a.type_id as assessment_type_id,
a.subject_id as assessment_subject_id,
a.school_year as assessment_school_year,
aa.min_score,
aa.max_score,
aa.cut_point_1,
aa.cut_point_2,
aa.cut_point_3
from exam e
join student_group_membership sgm on e.student_id=sgm.student_id
join asmt a on e.asmt_id=a.id
join school s on e.school_id=s.id
left join asmt_score aa on e.asmt_id=aa.asmt_id
where e.school_year=:school_year
and sgm.student_group_id=:group_id
and (0=:group_subject_id or a.subject_id=:group_subject_id)
and (1=:statewide or s.district_id in (:district_ids) or e.school_id in (:school_ids))
group by e.asmt_id
order by e.completed_at desc
-- /groups/{groupId}/assessment/{assessmentId}/exams
-- /groups/{groupId}/assessment/{assessmentId}/iabexams
-- gets all the exams for a given group assessment
-- ** This query is the same as the current path
select e.*,
st.id as student_id,
st.last_or_surname as student_last_name,
st.first_name as student_first_name,
st.gender_id as student_gender_id
from exam e
join student_group_membership sgm on e.student_id=sgm.student_id
join student st on e.student_id=st.id
join asmt a on e.asmt_id=a.id
join school s on e.school_id=s.id
where e.asmt_id=:assessment_id
and e.school_year=:school_year
and sgm.student_group_id=:group_id
and (0=:group_subject_id or a.subject_id=:group_subject_id)
and (1=:statewide or s.district_id in (:district_ids) or e.school_id in (:school_ids))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment