SQL write a query, question from interview problem

April 15, 2022

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.

>Solution :

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);

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;