How to get the most popular film category for every city PostgreSQL

Question

Here is the schema for database

Here is the code that i wrote:

select q.cName,q.ctgName  , max(q.count) as max_count
from (
select ct.city as cName ,ctg.name as ctgName,  count(ctg.name) as count
    from city as ct
    join address as addr on ct.city_id = addr.city_id
    join customer as cs on cs.address_id = addr.address_id
    join rental as r on r.customer_id = cs.customer_id
    join inventory as i on i.inventory_id = r.inventory_id
    join film as f on f.film_id = i.film_id
    join film_category as fc on fc.film_id = f.film_id
    join category as ctg on ctg.category_id = fc.category_id

    group by (ct.city,ctg.name)
    order by count desc
) q
group by (q.cName,q.ctgName)
order by(q.cName) desc

and the result i get:

When i remove q.ctgName parts it works fine but in that case , i am not able to fetch category names. How can i solve this. Im using PostgreSQL11

Answer 1

Your inner query looks fine, although with this over-normalized model, the Film table is not strictly needed in this query.

So to really simplify this I would add one column to your inner query, the city rank of each category. Then your outer query can simply select all records where city rank = 1.

, rank() over (Partition By Ct.city Order by count(*) desc) as cityrank

Thus,

Select * from (
    select ct.city as cName ,ctg.name as ctgName,  count(ctg.name) as count
        , rank() over (Partition By Ct.city Order by count(*) desc) as cityrank
    from city as ct
    join address as addr on ct.city_id = addr.city_id
    join customer as cs on cs.address_id = addr.address_id
    join rental as r on r.customer_id = cs.customer_id
    join inventory as i on i.inventory_id = r.inventory_id
    join film as f on f.film_id = i.film_id
    join film_category as fc on fc.film_id = f.film_id
    join category as ctg on ctg.category_id = fc.category_id
    group by (ct.city,ctg.name)
)
Where cityrank=1
Order by cName

Note that rank() can produce two (or more) number one categories when the counts are the same.