At the beginning I need to mention that I am not very good at SQL so I would like to ask you.
Here is a tables relation:
What I need to do with them is “Show name, surname and number of movies those directors (Reżyser means director), who made more movies that the average number is for all directors. Sort results from the biggest to the smallest.”
Here’s what I tried:
select r.NAME, r.SURNAME, count(*) as counter group by (r.NAME, r.SURNAME) having count > avg(*) orderby DESC;
but I got only 2/10 points from recruiter.
select name, count(*) from director d inner join film f on f.id_rez = d.id_rez group by d.id_rez having count(*) > (select count(id_rez) * 1.0 / count(distinct id_rez) from film) order by count(*) desc;
with summary as ( select id_rez, count(*) as rez_cnt, count(*) * 1.0 / count() over () as rez_avg from film where id_rez is not null group by id_rez ) select * from summary s inner join director d on d.id_rez = s.id_rez where rez_cnt > rez_avg order by rez_cnt desc;