TESTE SQL - QUESTÃO 1 - LIA

TesteSQL_QUESTAO_1.sql

-- **Parte A: Quantidade de alunos matriculados e valor total das matrículas**
SELECT 
    s.name AS school_name,
    st.enrolled_at::date AS enrollment_date,
    COUNT(st.id) AS total_students,
    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%'  -- Filtra cursos que começam com "data"
GROUP BY 
    s.name, st.enrolled_at::date
ORDER BY 
    enrollment_date DESC;

-- **Parte B: Soma acumulada, média móvel de 7 e 30 dias da quantidade de alunos**
WITH daily_enrollment AS (
    SELECT 
        s.name AS school_name,
        st.enrolled_at::date AS enrollment_date,
        COUNT(st.id) AS total_students
    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 cursos que começam com "data"
    GROUP BY 
        s.name, st.enrolled_at::date
)
SELECT 
    school_name,
    enrollment_date,
    total_students,
    SUM(total_students) OVER (PARTITION BY school_name ORDER BY enrollment_date) AS cumulative_students,
    AVG(total_students) OVER (PARTITION BY school_name ORDER BY enrollment_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7_days,
    AVG(total_students) OVER (PARTITION BY school_name ORDER BY enrollment_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS moving_avg_30_days
FROM 
    daily_enrollment
ORDER BY 
    enrollment_date DESC;