Yearly Billboard Revenue per Panel

PandasHardSenior level~10 min

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).

`panels` (`panels.csv`): `panel_id`, `panel_name` — one billboard panel per row.

`contracts` (`contracts.csv`): `panel_id`, `window_start`, `window_end`, `daily_rate` — each row is a rental 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 the columns `panel_id`, `panel_name`, `report_year` (as text) and `revenue`. Order by `panel_id` then `report_year`.

Input data

Example rows — the live problem includes the full dataset.

panels

panel_id	panel_name
1	Harbor Gate
2	Maple Cross
3	River Bend

contracts

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 (Pandas (Python))

import pandas as pd

def yearly_panel_revenue(panels: pd.DataFrame, contracts: pd.DataFrame) -> pd.DataFrame:
    # Your code here
    return panels

Solve this Pandas question free

Write Pandas (Python) and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

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.