SQL write a query, question from interview problem

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:

tables

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.

Answer

Classic sql:

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;

Modern sql:

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;