Разбор экзамена по SQL Т-банка

Разбор всех направлений выложен только на наших курсах. До конца дня действует льготная цена 13450 8950. Записываемся!

@ProdAnalysis

задача 1

select count(distinct student_id)
from (
select student_id
from enrollments
where status = 'completed'
) t

задача 2

select s.city
from students s
join enrollments e on s.student_id = e.student_id
where status = 'completed'
group by city
order by sum(e.progress_pct) desc, s.city asc
limit 1;

задача 3

select round(avg(rating)::numeric, 2)
from reviews;

задача 4

with cte_1 as (
select course, student_id, count(student_id) as cnt
from enrollments
where status = 'completed'
group by course, student_id
), cte_2 as (
select course, sum(case when cnt >= 2 then 1 else 0 end) as repeat_students, sum(cnt) as completed
from cte_1
group by course
)
select course
from cte_2
order by repeat_students desc, completed desc, course asc
limit 1;