SQL – Top Revenue Grossed By Year In Movies

SQL query to find the highest revenue movie for each year, sorted by year in descending order

Understand the Problem

Problem Statement

Given a database table named movie containing movie details, write an SQL query to find the top revenue grossed in every year present in the table.

The movie table has the following structure:

CREATE TABLE movie(id int, name VARCHAR(100), revenue int, releaseyear int);

The revenue is stored in millions. The query should return the maximum revenue for each release year, sorted by release year in descending order.

Output Requirements:

The first column must be releaseyear
The second column must be the maximum revenue for that year
Results must be sorted by releaseyear in descending order

Constraints

Table name: movie
Column names: id, name, revenue, releaseyear
Data types: id (int), name (VARCHAR(100)), revenue (int), releaseyear (int)
Assume no two movies will have the same maximum revenue in a given year
Table may contain multiple movies from the same year
Revenue values are positive integers representing millions

Examples

Example 1

Input

Sample data in movie table:

| id | name | revenue | releaseyear |
|----|------|---------|-------------|
| 1  | Movie A | 100    | 2020        |
| 2  | Movie B | 150    | 2020        |
| 3  | Movie C | 200    | 2021        |
| 4  | Movie D | 120    | 2021        |
| 5  | Movie E | 300    | 2022        |

Output

| releaseyear | revenue |
|-------------|---------|
| 2022        | 300     |
| 2021        | 200     |
| 2020        | 150     |

Explanation

The query groups movies by release year and finds the maximum revenue for each year: - 2020: Maximum revenue is 150 (Movie B) - 2021: Maximum revenue is 200 (Movie C) - 2022: Maximum revenue is 300 (Movie E) Results are sorted by release year in descending order (2022, 2021, 2020).

Example 2

Input

Sample data in movie table:

| id | name | revenue | releaseyear |
|----|------|---------|-------------|
| 1  | Action | 500    | 2019        |
| 2  | Drama  | 300    | 2019        |
| 3  | Comedy | 400    | 2020        |
| 4  | Thriller | 600  | 2020        |

Output

| releaseyear | revenue |
|-------------|---------|
| 2020        | 600     |
| 2019        | 500     |

Explanation

The query groups movies by release year and finds the maximum revenue for each year: - 2019: Maximum revenue is 500 (Action movie) - 2020: Maximum revenue is 600 (Thriller movie) Results are sorted by release year in descending order (2020, 2019).

Solution

-- SQL Query Solution
SELECT releaseyear, max(revenue)
FROM movie
GROUP BY releaseyear
ORDER BY releaseyear DESC;

Time:O(n log n)

Space:O(k)

Approach:

This is a pure SQL solution, not a C program. SQL is a declarative language used for database queries.

Explanation:

SELECT releaseyear, max(revenue): Specifies the columns to return - release year and maximum revenue
FROM movie: Specifies the source table
GROUP BY releaseyear: Groups all rows by the releaseyear column, creating groups for each unique year
ORDER BY releaseyear DESC: Sorts the result set by releaseyear in descending order (highest values first)

The max() function is an aggregate function that returns the highest value within each group created by GROUP BY.

Visual Explanation

Loading diagram...

All Problems