Skip to content

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;