Last active
March 31, 2025 20:36
-
-
Save pedrosteinmuller/bc2659136b2f48beb0c5de07cc2f2228 to your computer and use it in GitHub Desktop.
Lia - Junior Data Analyst - Teste técnico - SQL - Questão 1 - Candidato Pedro Steinmuller Pimentel
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Teste SQL Questão 1 | |
| item a) | |
| SELECT | |
| s.name AS school_name, | |
| st.enrolled_at::date AS enrollment_date, | |
| COUNT(st.id) AS students_count, | |
| SUM(c.price) AS total_price | |
| FROM | |
| students st | |
| JOIN | |
| courses c ON st.course_id = c.id | |
| JOIN | |
| schools s ON c.school_id = s.id | |
| WHERE | |
| c.name LIKE 'data%' | |
| GROUP BY | |
| s.name, st.enrolled_at::date | |
| ORDER BY | |
| enrollment_date DESC; | |
| item b) | |
| WITH student_counts AS ( | |
| SELECT | |
| s.name AS school_name, | |
| st.enrolled_at::date AS enrollment_date, | |
| COUNT(st.id) AS students_count | |
| FROM | |
| students st | |
| JOIN | |
| courses c ON st.course_id = c.id | |
| JOIN | |
| schools s ON c.school_id = s.id | |
| WHERE | |
| c.name LIKE 'data%' -- Filtra os cursos que começam com "data" | |
| GROUP BY | |
| s.name, st.enrolled_at::date | |
| ) | |
| SELECT | |
| school_name, | |
| enrollment_date, | |
| students_count, | |
| SUM(students_count) OVER (PARTITION BY school_name ORDER BY enrollment_date) AS cumulative_sum, | |
| AVG(students_count) OVER (PARTITION BY school_name ORDER BY enrollment_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days, | |
| AVG(students_count) OVER (PARTITION BY school_name ORDER BY enrollment_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) ASavg_30_days | |
| FROM | |
| student_counts | |
| ORDER BY | |
| school_name, enrollment_date DESC; | |
| -------------------------------------------------- | |
| - OVER para permitir calcular valores agregados; | |
| - PARTITION BY para dividir os dados em grupos. | |
| - ROWS BETWEEN 6 PRECEDING AND CURRENT ROW → Considera os últimos 7 dias (o atual + 6 anteriores) | |
| ex: AVG(students_count) OVER (PARTITION BY school_name → Calcula a média separadamente para cada escola. | |
| consultas realizadas: | |
| https://www.dio.me/articles/a-funcao-over-partition-by-em-sql-um-guia-completo | |
| https://www.geeksforgeeks.org/sql-rows-between/ | |
| -------------------------------------------------- |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment