# DOCUMENTAÇÃO DA MIGRAÇÃO LOOKER-METABASE

Oi pessoal! A ideia é seguirmos registrando aqui as queries SQL do nosso mapeamento no Looker.
## Redes Privadas
### Dashboard Rede SAE
### [Dashboard Rede Bradesco](https://hackmd.io/@7H4O7jxhT3Gb0qFkpOEJgw/Dashboard_Bradesco/edit)
### [Dashboard Rede SESI BA](https://hackmd.io/2VElUHv7SEeDinnugNsrbg)

### [Dashboard Rede SESI MS](https://hackmd.io/NZkB1grBQiCM1kZVz0BE7w)

### Dashboard Rede Escola Mais

1. Atividades recentes:
- Imagem:


- Filtro:

- Query:

-- use existing test_engagement_facts in looker_scratch.LR_UM0LJ1628515299184_test_engagement_facts
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 looker_scratch.LR_UM0LJ1628515299184_test_engagement_facts AS test_engagement_facts ON tests.test_id = test_engagement_facts.test_id
LEFT JOIN letrus.networks  AS networks ON schools.network_id = networks.network_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' OR (tests.review_type ) = 'AI-Competences')) AND (((test_engagement_facts.status_temporal) = 'Aberta' OR (test_engagement_facts.status_temporal) = 'Encerrada recentemente' OR (test_engagement_facts.status_temporal) = 'Terminando') AND ((networks.network_name ) = 'Escola Mais' AND (not (schoolgroups.is_pre = 1) and schoolgroups.school_year = 2021)))
LIMIT 500

## Dashboard Rede Pública: Espirito Santo

## Dashboards de Assessoria: Carteira (Vini)

* Classificação de score:

case when ${healthscore} >= 7 then 'Alto'
else case when ${healthscore} >= 5 then 'Medio' 
else 'Baixo'

### Profs sem acesso há mais de 25 dias


* Detalhe: adicionei uma verificação de que o professor tenha um user role do ano atual, retirar dos resultados professores que não tem mais contato ativo com a Letrus

select user.last_login from auth_user user

left join compositions_userrole user_role on user_role.user_id = user.id
left join compositions_roletype role_type on user_role.role_id = role_type.id
left join compositions_schoolgroup school_group on user_role.school_group_id = school_group.id


where
    role_type.name = "teacher"
    and school_group.school_year = 2021
    and datediff(now(), user.last_login) >= 25

### Escolas em atenção: Escolas com score médio ou baixo


select {nome da escola, score atual da escola, ..} from schools

left join schools.long_name
left join auth_user teacher on teacher.id = usr_role.user_id


left join school_engagement_facts on school_engagement_facts.school_id = schools.id
left join school_reviewscore_facts on school_engagement_facts.school_id = school_reviewscore_facts.school_id
left join school_adherence_facts on school_adherence_facts.school_id = school_reviewscore_facts.school_id
left join school_healthscore score on score.school_id = schools.id
inner join contract_redux_facts as contracts on school_engagement_facts.school_id = contracts.school_id

where score.healthscore_tier <> "Alto"

### Turmas há muito tempo sem atividade: Turmas em que aderência de contrato está abaixo de 60%

Dashboard com filtro errado:


### Saúde das escolas


Query sem filtro, mas não temos ainda tabelas equivalentes no Athena/Metabase

### Gráfico de pizza para classificações de score


### Atividades futuras e dos últimos 30 dias


A query está, pelo menos por enquanto, quebrada no Looker.