Highest-Spend Advertisement
Capital OneINTERNNEW GRADOAProblem statement
An advertising company tracks advertisements, their long and short video versions, broadcasting platforms, and per-video broadcasting statistics.
Find the advertisement on which the company spent the most money. For a statistics row, the broadcasting cost is watch_count * price_per_watch. Count a row only when its platform_id exists in platforms and its video_id is either the long or short version of an advertisement.
Sum the qualifying costs across both versions and every recognized platform for each advertisement.
Result
Return exactly one row with the winning ad_name. Every testcase has a unique highest-spend advertisement.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
ads
Advertisement names and the video IDs of their long and short versions.
| Column | Type | Nullable | Description |
|---|---|---|---|
| ad_idPK | Integer | No | — |
| ad_name | Text | No | — |
| long_version_video_id | Integer | No | — |
| short_version_video_id | Integer | No | — |
videos
Video-file paths and durations in seconds.
| Column | Type | Nullable | Description |
|---|---|---|---|
| video_idPK | Integer | No | — |
| path | Text | No | — |
| duration | Integer | No | — |
platforms
Recognized broadcasting platforms; contact details may be missing.
| Column | Type | Nullable | Description |
|---|---|---|---|
| platform_idPK | Integer | No | — |
| contact_mail | Text | Yes | — |
| website | Text | Yes | — |
ads_statistics
Broadcasting metrics, including exact price per watch.
| Column | Type | Nullable | Description |
|---|---|---|---|
| platform_id | Integer | No | — |
| video_id | Integer | No | — |
| watch_count | Integer | No | — |
| total_time_watched | Integer | No | — |
| price_per_watch | Decimal | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| ad_name | Text | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Each input table contains at most
2000rows. ad_id,ad_name, and every advertisement version ID are unique.- Every long and short version ID exists in
videos, and the two versions of an advertisement are distinct. watch_countandtotal_time_watchedare non-negative signed 64-bit integers.price_per_watchis a non-negative exact decimal value.- Statistics may name an unrecognized platform or a video that is not an advertisement version; those rows do not contribute.
- At least one advertisement has a qualifying statistics row, and the highest total spend is unique.
- Every per-row cost and per-advertisement total fits in
DECIMAL(38, 12).