SQL Quest › SQL Interview Questions › Aggregation & Grouping

Genre Box Office Report

MediumFreeQuerying BasicsAggregation & GroupingNULL HandlingConditional Logic

Build a genre-level box office report for all genres. Show: genre, total_films, films_with_revenue (count where revenue is not NULL), total_revenue (treat NULL as 0 using COALESCE, rounded to 1 dec), and avg_revenue_known_films (average of non-NULL revenues only, 1 dec). Sort by total_revenue descending.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

movies

id	title	year	genre	rating	votes	revenue_millions	runtime	director
1	Guardians of the Galaxy	2014	Action	8.1	757074	333.13	121	James Gunn
2	Prometheus	2012	Adventure	7	485820	126.46	124	Ridley Scott
3	Split	2016	Horror	7.3	157606	138.12	117	M. Night Shyamalan

Expected output: All genres with correct NULL-aware revenue totals

Hint

COALESCE(revenue_millions, 0) converts NULLs to 0 for SUM. AVG(revenue_millions) naturally skips NULLs for the known-films average

Concepts

SELECT GROUP BY Aggregation NULL Handling ORDER BY CASE HAVING

Practise the topic: SQL practice questions · GROUP BY exercises · NULL handling practice · CASE WHEN practice

In these company practice sets

Snowflake · Spotify

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Genre Box Office Report

Schema

Hint

Concepts

In these company practice sets

Related questions