lia-test-question1-ab.sql

-- a CTE já apresenta o resultado da questão 1a
with student_price_per_school as (
    select
    count(students.id) as qnty_students, 
    students.enrolled_at,
    schools.name as school_name,
    sum(price) as enrollment_price 
from
    courses
    join schools on courses.school_id = schools.id
    join students on students.course_id = courses.id
where
    courses.name not ilike 'data%' --se for para ser apenas os com data, colocar ilike 'data%'
group by
    schools.name,
    students.enrolled_at
order by
    schools.name,
    students.enrolled_at desc
)
select
    school_name,
    enrolled_at,
    trunc((sum(qnty_students) over (partition by school_name, enrolled_at)), 2) as running_total,
    trunc((avg(qnty_students) over (partition by school_name, enrolled_at order by enrolled_at range between interval '6 days' preceding and current row)), 2) as rolling_avg_7,
    trunc((avg(qnty_students) over (partition by school_name, enrolled_at order by enrolled_at range between interval '29 days' preceding and current row)), 2) as rolling_avg_30
from student_price_per_school