## Dashboard Rede Bradesco

1. Atividades recentes
- Imagem:
- Filtros Looker:

- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    COUNT(DISTINCT tests.test_id ) AS "tests.count_tests"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.manual_review = 1 and (tests.review_type = 'AI-Competences') = false) AND ((test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Terminando') AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)
LIMIT 500

2. Atividades OK
- Imagem:
- Filtros Looker:

- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    COUNT(DISTINCT tests.test_id ) AS "tests.count_tests"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.manual_review = 1 and (tests.review_type = 'AI-Competences') = false)) AND (((test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Terminando') AND ((case
        when (case
    when test_engagement_facts.status_temporal = 'Terminando' and test_engagement_facts.test_engagement >= 0.75
    then true
    when test_engagement_facts.status_temporal = 'Encerrada recentemente' and test_engagement_facts.test_engagement >= 0.75
    then true
    when test_engagement_facts.status_temporal = 'Aberta'
    then true
    else false
    end) then 'Ok'
        when (case
    when test_engagement_facts.status_temporal = 'Terminando' and test_engagement_facts.test_engagement >= 0.4 and test_engagement_facts.test_engagement < 0.75
    then true
    else false
    end) then 'Atenção'
        when (case
    when test_engagement_facts.status_temporal = 'Terminando'
    then
        case
        when test_engagement_facts.test_engagement < 0.4
        then true
        else false
        end
    when test_engagement_facts.status_temporal = 'Encerrada recentemente'
    then
        case
        when test_engagement_facts.test_engagement < 0.75
        then true
        else false
        end
    else false
    end) then 'Crítico'
        else '-' end ) = 'Ok' AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)))
LIMIT 500

3. Atividades em atenção
- Imagem:
- Filtros Looker:

- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    COUNT(DISTINCT tests.test_id ) AS "tests.count_tests"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.manual_review = 1 and (tests.review_type = 'AI-Competences') = false)) AND (((test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Terminando') AND ((case
        when (case
    when test_engagement_facts.status_temporal = 'Terminando' and test_engagement_facts.test_engagement >= 0.75
    then true
    when test_engagement_facts.status_temporal = 'Encerrada recentemente' and test_engagement_facts.test_engagement >= 0.75
    then true
    when test_engagement_facts.status_temporal = 'Aberta'
    then true
    else false
    end) then 'Ok'
        when (case
    when test_engagement_facts.status_temporal = 'Terminando' and test_engagement_facts.test_engagement >= 0.4 and test_engagement_facts.test_engagement < 0.75
    then true
    else false
    end) then 'Atenção'
        when (case
    when test_engagement_facts.status_temporal = 'Terminando'
    then
        case
        when test_engagement_facts.test_engagement < 0.4
        then true
        else false
        end
    when test_engagement_facts.status_temporal = 'Encerrada recentemente'
    then
        case
        when test_engagement_facts.test_engagement < 0.75
        then true
        else false
        end
    else false
    end) then 'Crítico'
        else '-' end ) = 'Atenção' AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)))
LIMIT 500

4. Atividades criticas
- Imagem:
- Filtros Looker:

- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT
    COUNT(DISTINCT tests.test_id ) AS "tests.count_tests"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.manual_review = 1 and (tests.review_type = 'AI-Competences') = false)) AND (((test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Terminando') AND ((case
        when (case
    when test_engagement_facts.status_temporal = 'Terminando' and test_engagement_facts.test_engagement >= 0.75
    then true
    when test_engagement_facts.status_temporal = 'Encerrada recentemente' and test_engagement_facts.test_engagement >= 0.75
    then true
    when test_engagement_facts.status_temporal = 'Aberta'
    then true
    else false
    end) then 'Ok'
        when (case
    when test_engagement_facts.status_temporal = 'Terminando' and test_engagement_facts.test_engagement >= 0.4 and test_engagement_facts.test_engagement < 0.75
    then true
    else false
    end) then 'Atenção'
        when (case
    when test_engagement_facts.status_temporal = 'Terminando'
    then
        case
        when test_engagement_facts.test_engagement < 0.4
        then true
        else false
        end
    when test_engagement_facts.status_temporal = 'Encerrada recentemente'
    then
        case
        when test_engagement_facts.test_engagement < 0.75
        then true
        else false
        end
    else false
    end) then 'Crítico'
        else '-' end ) = 'Crítico' AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)))
LIMIT 500

5. Acompanhamento das turmas recentes
- Imagem:

- Filtros Looker:

- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    schoolgroups.school_group_name  AS "schoolgroups.school_group_name",
    tests.name  AS "tests.test_name",
    genre.name  AS "genre.genre_name",
    test_engagement_facts.status_temporal AS "test_engagement_facts.status_temporal",
        (DATE_FORMAT((tests.start_datetime  AT TIME ZONE 'Brazil/East'), '%Y-%m-%d %T')) AS "tests.start_datetime_time",
        (DATE_FORMAT((tests.end_datetime  AT TIME ZONE 'Brazil/East'), '%Y-%m-%d %T')) AS "tests.end_datetime_time",
    test_engagement_facts.test_engagement AS "test_engagement_facts.test_engagement",
    tests.test_id  AS "tests.test_id"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.manual_review = 1 and (tests.review_type = 'AI-Competences') = false) AND ((test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Terminando') AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)
GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10
ORDER BY
    2
LIMIT 500

6. Engajamento recente por unidade
- Imagem:

- Filtros Looker:

- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    AVG(test_engagement_facts.test_engagement) AS "test_engagement_facts.avg_test_engagement"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human' AND ((test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Terminando') AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)
GROUP BY
    1,
    2
ORDER BY
    3 DESC
LIMIT 500

-- sql for creating the total and/or determining pivot columns
SELECT
    AVG(test_engagement_facts.test_engagement) AS "test_engagement_facts.avg_test_engagement"
FROM letrus.schools  AS schools
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON schools.school_id = schoolgroups.school_id
LEFT JOIN letrus.tests  AS tests ON schoolgroups.school_group_id = tests.school_group_id
LEFT JOIN looker_scratch.LR_UM0LJ1628616172575_test_engagement_facts AS test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
WHERE ((((( tests.end_datetime  )) >= ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP)), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP))) AND (( tests.end_datetime  )) < ((CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('day', 10, DATE_ADD('day', -9, CAST(CAST(DATE_TRUNC('DAY', (NOW() AT TIME ZONE 'Brazil/East')) AS DATE) AS TIMESTAMP))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) OR ( tests.end_datetime  ) >= (CAST(CONCAT(FORMAT_DATETIME(DATE_ADD('minute', 0, DATE_TRUNC('MINUTE', (NOW() AT TIME ZONE 'Brazil/East'))), 'yyyy-MM-dd HH:mm:ss.SSS'), ' Brazil/East') AS TIMESTAMP)))) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human' AND ((test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Terminando') AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)
LIMIT 1

7. Média de notas por unidade
- Imagem:

- Filtros Looker:


- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
GROUP BY
    1,
    2
ORDER BY
    3 DESC
LIMIT 500

8. Média 1ª Comp
- Imagem:

- Filtros Looker:


- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 97 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c1"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 500

9. Média 2ª Comp
- Imagem:

- Filtros Looker:


- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 98 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c2"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN looker_scratch.LR_UM0LJ1628711302065_test_engagement_facts AS test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 500

10. Média 3ª Comp

- Imagem:

- Filtros Looker:


- Query:

WITH
    test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 99 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c3"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 500

11. Média 4ª Comp
- Imagem:

- Filtros Looker:


- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 100 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c4"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 500

12. Média 5ª Comp
- Imagem:

- Filtros Looker:


- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 101 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c5"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 500

13. Nota Final

- Imagem:

- Filtros Looker:


- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND ((NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL) AND (tests.review_type ) = 'Human')) AND ((genre.name ) = 'ENEM' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND (((test_engagement_facts.status_temporal) = 'Encerrada há algum tempo' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 500

14. Acompanhamento de atividades e competências
- Imagem:

- Filtros Looker:

- Query 1:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    schoolgroups.school_group_name  AS "schoolgroups.school_group_name",
    tests.test_id  AS "tests.test_id",
    tests.name  AS "tests.test_name",
    genre.name  AS "genre.genre_name",
    (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_engagement  ELSE NULL END
,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0))) ) - SUM(DISTINCT (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0)))) )  AS DOUBLE) / CAST((1000000*1.0) AS DOUBLE), 0) / NULLIF(COUNT(DISTINCT CASE WHEN  tests.end_datetime < localtimestamp   AND  test_engagement_facts.test_engagement  IS NOT NULL THEN  test_engagement_facts.test_id  ELSE NULL END), 0)) AS "test_engagement_facts.avg_finished_test_engagement",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 97 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c1",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 98 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c2",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 99 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c3",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 100 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c4",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 101 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c5",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL)) AND ((tests.review_type ) = 'Human' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND ((test_engagement_facts.status_temporal) IN ('Encerrada há algum tempo', 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
GROUP BY
    1,
    2,
    3,
    4,
    5,
    6
ORDER BY
    2
LIMIT 500

- Query 2:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT
    (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_engagement  ELSE NULL END
,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0))) ) - SUM(DISTINCT (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0)))) )  AS DOUBLE) / CAST((1000000*1.0) AS DOUBLE), 0) / NULLIF(COUNT(DISTINCT CASE WHEN  tests.end_datetime < localtimestamp   AND  test_engagement_facts.test_engagement  IS NOT NULL THEN  test_engagement_facts.test_id  ELSE NULL END), 0)) AS "test_engagement_facts.avg_finished_test_engagement",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 97 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c1",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 98 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c2",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 99 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c3",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 100 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c4",
    AVG(CASE
      WHEN  competencegradeitem.competence_id   = 101 THEN  competencegradeitem.points
      ELSE null END) AS "competencegradeitem.grade_c5",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL)) AND ((tests.review_type ) = 'Human' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND ((test_engagement_facts.status_temporal) IN ('Encerrada há algum tempo', 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
LIMIT 1

15. Acompanhamento de atividades por turma
- Imagem:

- Filtros Looker:


- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT * FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY z___min_rank) as z___pivot_row_rank, RANK() OVER (PARTITION BY z__pivot_col_rank ORDER BY z___min_rank) as z__pivot_col_ordering, CASE WHEN z___min_rank = z___rank THEN 1 ELSE 0 END AS z__is_highest_ranked_cell FROM (
SELECT *, MIN(z___rank) OVER (PARTITION BY "schools.school_id","schools.school_name","schoolgroups.school_group_name") as z___min_rank FROM (
SELECT *, RANK() OVER (ORDER BY "schools.school_name" ASC, z__pivot_col_rank, "schools.school_id", "schoolgroups.school_group_name") AS z___rank FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY CASE WHEN "tests.test_name" IS NULL THEN 1 ELSE 0 END, "tests.test_name") AS z__pivot_col_rank FROM (
SELECT
    tests.name  AS "tests.test_name",
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    schoolgroups.school_group_name  AS "schoolgroups.school_group_name",
    (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_engagement  ELSE NULL END
,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0))) ) - SUM(DISTINCT (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0)))) )  AS DOUBLE) / CAST((1000000*1.0) AS DOUBLE), 0) / NULLIF(COUNT(DISTINCT CASE WHEN  tests.end_datetime < localtimestamp   AND  test_engagement_facts.test_engagement  IS NOT NULL THEN  test_engagement_facts.test_id  ELSE NULL END), 0)) AS "test_engagement_facts.avg_finished_test_engagement",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL)) AND ((tests.review_type ) = 'Human' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND ((test_engagement_facts.status_temporal) IN ('Encerrada há algum tempo', 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
GROUP BY
    1,
    2,
    3,
    4) ww
) bb WHERE z__pivot_col_rank <= 16384
) aa
) xx
) zz
 WHERE (z__pivot_col_rank <= 50 OR z__is_highest_ranked_cell = 1) AND (z___pivot_row_rank <= 500 OR z__pivot_col_ordering = 1) ORDER BY z___pivot_row_rank

16. Acompanhamento de atividades por unidade
- Imagem:
- Filtros Looker:


- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)
    
SELECT * FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY z___min_rank) as z___pivot_row_rank, RANK() OVER (PARTITION BY z__pivot_col_rank ORDER BY z___min_rank) as z__pivot_col_ordering, CASE WHEN z___min_rank = z___rank THEN 1 ELSE 0 END AS z__is_highest_ranked_cell FROM (
SELECT *, MIN(z___rank) OVER (PARTITION BY "schools.school_id","schools.school_name") as z___min_rank FROM (
SELECT *, RANK() OVER (ORDER BY "schools.school_name" ASC, z__pivot_col_rank, "schools.school_id") AS z___rank FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY CASE WHEN "tests.test_name" IS NULL THEN 1 ELSE 0 END, "tests.test_name") AS z__pivot_col_rank FROM (
SELECT
    tests.name  AS "tests.test_name",
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_engagement  ELSE NULL END
,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0))) ) - SUM(DISTINCT (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0)))) )  AS DOUBLE) / CAST((1000000*1.0) AS DOUBLE), 0) / NULLIF(COUNT(DISTINCT CASE WHEN  tests.end_datetime < localtimestamp   AND  test_engagement_facts.test_engagement  IS NOT NULL THEN  test_engagement_facts.test_id  ELSE NULL END), 0)) AS "test_engagement_facts.avg_finished_test_engagement",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL)) AND ((tests.review_type ) = 'Human' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND ((test_engagement_facts.status_temporal) IN ('Encerrada há algum tempo', 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
GROUP BY
    1,
    2,
    3) ww
) bb WHERE z__pivot_col_rank <= 16384
) aa
) xx
) zz
 WHERE (z__pivot_col_rank <= 50 OR z__is_highest_ranked_cell = 1) AND (z___pivot_row_rank <= 500 OR z__pivot_col_ordering = 1) ORDER BY z___pivot_row_rank

17. Acompanhamento de turmas
- Imagem:
- Filtros Looker:

- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT * FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY z___min_rank) as z___pivot_row_rank, RANK() OVER (PARTITION BY z__pivot_col_rank ORDER BY z___min_rank) as z__pivot_col_ordering, CASE WHEN z___min_rank = z___rank THEN 1 ELSE 0 END AS z__is_highest_ranked_cell FROM (
SELECT *, MIN(z___rank) OVER (PARTITION BY "schools.school_id","schools.school_name","schoolgroups.school_group_name") as z___min_rank FROM (
SELECT *, RANK() OVER (ORDER BY "schools.school_name" ASC, z__pivot_col_rank, "schools.school_id", "schoolgroups.school_group_name") AS z___rank FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY CASE WHEN "genre.genre_name" IS NULL THEN 1 ELSE 0 END, "genre.genre_name") AS z__pivot_col_rank FROM (
SELECT
    genre.name  AS "genre.genre_name",
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    schoolgroups.school_group_name  AS "schoolgroups.school_group_name",
    (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_engagement  ELSE NULL END
,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0))) ) - SUM(DISTINCT (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0)))) )  AS DOUBLE) / CAST((1000000*1.0) AS DOUBLE), 0) / NULLIF(COUNT(DISTINCT CASE WHEN  tests.end_datetime < localtimestamp   AND  test_engagement_facts.test_engagement  IS NOT NULL THEN  test_engagement_facts.test_id  ELSE NULL END), 0)) AS "test_engagement_facts.avg_finished_test_engagement",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL)) AND ((tests.review_type ) = 'Human' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND ((test_engagement_facts.status_temporal) IN ('Encerrada há algum tempo', 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
GROUP BY
    1,
    2,
    3,
    4) ww
) bb WHERE z__pivot_col_rank <= 16384
) aa
) xx
) zz
 WHERE (z__pivot_col_rank <= 50 OR z__is_highest_ranked_cell = 1) AND (z___pivot_row_rank <= 500 OR z__pivot_col_ordering = 1) ORDER BY z___pivot_row_rank

18. Acompanhamento de série
- Imagem:

- Filtros Looker:


- Query:

WITH test_engagement_facts AS (SELECT
    tests_extended.test_id  AS "test_id",
    case
          when (not (tests_extended.start_datetime < localtimestamp)) then 'Não iniciada'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Aberta'
          when ((tests_extended.start_datetime < localtimestamp) and not (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Terminando'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada recentemente'
          when ((tests_extended.start_datetime < localtimestamp) and (tests_extended.end_datetime < localtimestamp) and not ((date_diff('hour',localtimestamp,tests_extended.end_datetime)) < 48 and (date_diff('hour',localtimestamp,tests_extended.end_datetime)) > -480)) then 'Encerrada há algum tempo'
          else '-' end AS "status_temporal",
        CAST(COUNT(DISTINCT CASE WHEN (compositions.flag_finished = 1) AND (NOT COALESCE(compositions.deleted is not null , FALSE)) THEN compositions.composition_id  ELSE NULL END)  as DOUBLE) / nullif(COUNT(DISTINCT studentroles.user_id ), 0) AS "test_engagement"
FROM letrus.tests  AS tests_extended
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests_extended.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.compositions  AS compositions ON tests_extended.test_id = compositions.test_id
LEFT JOIN letrus.studentroles  AS studentroles ON schoolgroups.school_group_id = studentroles.school_group_id
WHERE (not (studentroles.deleted is not null) )
GROUP BY
    1,
    2)

SELECT * FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY z___min_rank) as z___pivot_row_rank, RANK() OVER (PARTITION BY z__pivot_col_rank ORDER BY z___min_rank) as z__pivot_col_ordering, CASE WHEN z___min_rank = z___rank THEN 1 ELSE 0 END AS z__is_highest_ranked_cell FROM (
SELECT *, MIN(z___rank) OVER (PARTITION BY "schools.school_id","schools.school_name","schoolgrades.school_grade_name") as z___min_rank FROM (
SELECT *, RANK() OVER (ORDER BY "schools.school_name" ASC, z__pivot_col_rank, "schools.school_id", "schoolgrades.school_grade_name") AS z___rank FROM (
SELECT *, DENSE_RANK() OVER (ORDER BY CASE WHEN "genre.genre_name" IS NULL THEN 1 ELSE 0 END, "genre.genre_name") AS z__pivot_col_rank FROM (
SELECT
    genre.name  AS "genre.genre_name",
    schools.school_id  AS "schools.school_id",
    schools.long_name  AS "schools.school_name",
    schoolgrades.school_grade_name  AS "schoolgrades.school_grade_name",
    (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_engagement  ELSE NULL END
,0)*(1000000*1.0)) AS DECIMAL(38,0))) + (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0))) ) - SUM(DISTINCT (CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))),1,14),16) AS DECIMAL(38, 0)) * CAST(10000000000 AS DECIMAL(38, 0)) + CAST(FROM_BASE(SUBSTR(TO_HEX(MD5(CAST(CAST(CASE WHEN  tests.end_datetime < localtimestamp   THEN  test_engagement_facts.test_id  ELSE NULL END
 AS VARCHAR) AS VARBINARY))), 17, 10), 16) AS DECIMAL(38, 0)))) )  AS DOUBLE) / CAST((1000000*1.0) AS DOUBLE), 0) / NULLIF(COUNT(DISTINCT CASE WHEN  tests.end_datetime < localtimestamp   AND  test_engagement_facts.test_engagement  IS NOT NULL THEN  test_engagement_facts.test_id  ELSE NULL END), 0)) AS "test_engagement_facts.avg_finished_test_engagement",
    AVG(5.0* competencegradeitem.points ) AS "competencegradeitem.total_points"
FROM letrus.compositions  AS compositions
LEFT JOIN letrus.tests  AS tests ON compositions.test_id = tests.test_id
LEFT JOIN letrus.instructions  AS instructions ON tests.theme_id = instructions.id
LEFT JOIN letrus.compositiongenre  AS genre ON instructions.genre_id = genre.id
LEFT JOIN letrus.compositionreviews  AS compositionreviews ON compositions.composition_id = compositionreviews.composition_id
LEFT JOIN test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.reviewcompetencegrade  AS reviewcompetencegrade ON reviewcompetencegrade.review_id = compositionreviews.review_id
LEFT JOIN letrus.competencegradeitem  AS competencegradeitem ON competencegradeitem.id = reviewcompetencegrade.grade_item_id
LEFT JOIN letrus.schoolgroups  AS schoolgroups ON tests.school_group_id = schoolgroups.school_group_id
LEFT JOIN letrus.schoolgrades  AS schoolgrades ON schoolgroups.school_grade_id = schoolgrades.school_grade_id
LEFT JOIN letrus.schools  AS schools ON schools.school_id = schoolgroups.school_id
WHERE (NOT (compositions.deleted is not null ) OR (compositions.deleted is not null ) IS NULL) AND (compositions.flag_finished = 1 ) AND ((compositions.flag_revision = 1  ) AND (NOT (tests.deleted is not null ) OR (tests.deleted is not null ) IS NULL)) AND ((tests.review_type ) = 'Human' AND (NOT (compositionreviews.deleted is not null ) OR (compositionreviews.deleted is not null ) IS NULL) AND ((compositionreviews.flag_finished = 1 ) AND ((test_engagement_facts.status_temporal) IN ('Encerrada há algum tempo', 'Encerrada recentemente') AND (schoolgroups.school_year ) = 2021)))
GROUP BY
    1,
    2,
    3,
    4) ww
) bb WHERE z__pivot_col_rank <= 16384
) aa
) xx
) zz
 WHERE (z__pivot_col_rank <= 50 OR z__is_highest_ranked_cell = 1) AND (z___pivot_row_rank <= 500 OR z__pivot_col_ordering = 1) ORDER BY z___pivot_row_rank

19. Acompanhamento de unidades
- Imagem:

- Filtros Looker:


- Query:

[link da query](https://colab.research.google.com/drive/1LBDFQLnYfPevKAsx_4Q06exgfONfeXV1?usp=sharing)

20. Histórico escolar
- Imagem:

- Filtros Looker:

- Query:

[link da query](https://colab.research.google.com/drive/1x7ovZPijYe9g7pnbeKB0P32bmZVciX4D?usp=sharing)