---
# System prepended metadata

title: DOCUMENTAÇÃO DA MIGRAÇÃO LOOKER-METABASE

---

# 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:

![](https://i.imgur.com/7jFiZ49.png)

- Filtro:
![](https://i.imgur.com/wWvFHrv.png)
![](https://i.imgur.com/Jkbumu4.png)

- Query:
```sql=
-- 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:

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

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

![](https://i.imgur.com/RMhxDSX.png)

* 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 

```sql=
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

![](https://i.imgur.com/WqjBQsD.png)

```sql
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:

![](https://i.imgur.com/g6lvI9r.png)

### Saúde das escolas

![](https://i.imgur.com/qt51Gyn.png)

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

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

![](https://i.imgur.com/CqBXgtb.png)

### Atividades futuras e dos últimos 30 dias

![](https://i.imgur.com/iL5pMQ7.png)

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