Yearly Billboard Revenue per Panel
Problem
An outdoor-advertising company rents billboard panels under contracts that may run across several calendar years. Each contract charges a flat daily rate for every day inside its window (both endpoints inclusive).
Table: `panels`
| Column | Type |
|-------------|---------|
| panel_id | int |
| panel_name | varchar |
`panel_id` is the primary key. Each row names a billboard panel.
Table: `contracts`
| Column | Type |
|--------------|------|
| panel_id | int |
| window_start | date |
| window_end | date |
| daily_rate | int |
Each row is a rental contract for a panel running from `window_start` to `window_end` inclusive, billed at `daily_rate` per day.
For the reporting years **2021, 2022, and 2023**, report the revenue earned on each panel in each year that it earned anything. Yearly revenue = (number of contract days that fall inside that calendar year) * daily_rate.
Return columns `panel_id`, `panel_name`, `report_year` (as text) and `revenue`. Order by `panel_id` then `report_year`.
Tables
Example rows — the live problem includes the full dataset.
| panel_id | panel_name |
|---|---|
| 1 | Harbor Gate |
| 2 | Maple Cross |
| 3 | River Bend |
| panel_id | window_start | window_end | daily_rate |
|---|---|---|---|
| 1 | 2022-01-25 | 2022-02-28 | 100 |
| 2 | 2021-12-01 | 2023-01-01 | 10 |
| 3 | 2022-12-01 | 2023-01-31 | 1 |
Expected output
Your answer should return 6 rows with the columns panel_id, panel_name, report_year, revenue.
Starter code (SQL)
SELECT *
FROM panels;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock