enrollment_rank (view)

1,667 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE VIEW enrollment_rank as 
with cte as (   
    select   
    department
    ,year
    ,sum(enrolled) as total_enrolled
    ,cast(avg(enrolled) as integer) as avg_class_size
    ,count(distinct instructor) as instructors 
    from   courses where enrolled > 1 and year < 2023 group by   department,   year   
) 
select 
    cte.* 
    ,printf("%.2f",
        sum(total_enrolled) over(partition by year order by year,total_enrolled desc)
        / sum(total_enrolled) over(partition by year)
    ) as cumulative 
    ,count(department) over(partition by year) as departments
    ,row_number() over(partition by year order by total_enrolled desc) as rank
from cte 
order by
    year desc
    ,total_enrolled desc;