query.sql

with assessment_block_index as (
    select
        entity_key as assessment_block_key,
        parent_key as step_key
    from entity_indices
    where entity_type = 'AssessmentBlock'
),
     step_index as (
         select
             entity_key as step_key,
             parent_key as skill_key
         from entity_indices
         where entity_type = 'Step'
     ),
     skill_index as (
         select
             entity_key as skill_key,
             parent_key as skillset_key
         from entity_indices
         where entity_type = 'Skill'
     ),
     hierarchy as (
         select
             abi.assessment_block_key,
             si.step_key,
             steps.title as step_title,
             ski.skill_key,
             skills.title as skill_title,
             ski.skillset_key,
             skillsets.skillset_title
         from assessment_block_index abi
              join step_index si on si.step_key = abi.step_key
              join skill_index ski on ski.skill_key = si.skill_key
              inner join steps on steps.key = si.step_key
              inner join skills on skills.key = ski.skill_key
              inner join skillsets on skillsets.key = ski.skillset_key
     ),
     counts as (
         select
             hierarchy.skillset_title,
             hierarchy.skill_title,
             hierarchy.skill_key,
             sum(failed_count) as failure_count,
             count(failed_at) as learner_count
         from assessment_block_progresses abp
              join hierarchy on hierarchy.assessment_block_key = abp.assessment_block_key
         group by 1, 2, 3
     )

select *,
       ROUND((failure_count / learner_count)::numeric,2)     as avg_failures_per_learner
from counts
order by avg_failures_per_learner desc;

select * from skillsets where id = 52;

select device_app_version from learners where id = 89039;

select distinct(type) from questions;

select * from calendar_events;
select * from calendar_invitees

select count(*) from learners where theory_completed_at is not null;
select count(*) from course_progresses where completed_at is not null;