Questão 1 de SQL

gistfile1.txt

Questão 1:

a)
SELECT h.name AS school_name, DATE(s.enrolled_at) AS data_matricula, COUNT (s.id) AS total_estud, SUM (c.price) AS valor_total
FROM students s
JOIN courses c ON s.cours_id = c.id
JOIN schools h ON c.school_id = s.id
WHERE c.name LIKE 'data%'
GROUP BY s.name, DATE(s.enrolled_at)
ORDER BY data_matricula DESC

b)
SELECT 
    h.name AS school_name,
    DATE(s.enrolled_at) AS data_matricula,
    COUNT(s.id) AS total_estud,
    SUM(c.price) AS valor_total,
    SUM(COUNT(s.id)) OVER (
        PARTITION BY h.name ORDER BY DATE(s.enrolled_at) ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS soma_acumulada,
    AVG(COUNT(s.id)) OVER (
        PARTITION BY s.name ORDER BY DATE(st.enrolled_at) ASC
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS media_sete_dias,
    AVG(COUNT(s.id)) OVER (
        PARTITION BY s.name ORDER BY DATE(st.enrolled_at) ASC
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS media_trinta_dias
FROM students s
JOIN courses c ON s.cours_id = c.id
JOIN schools h ON c.school_id = s.id