# Best Day for Ad Revenue

> One day of the month outperforms the rest.

Canonical URL: <https://datadriven.io/problems/best_day_for_ad_revenue>

Domain: SQL · Difficulty: medium · Seniority: L5

## Problem

The ad team wants to identify which day of the month shows the strongest click premium: the biggest gap between average revenue on clicked impressions versus non-clicked ones. For each calendar day (1 through 31), compute the overall average revenue and the peak single-impression revenue. Return only the one day where the click premium is widest.

## Worked solution and explanation

### What this is really asking

The output columns are day, avg_revenue, max_revenue, but the ranking key is a different quantity: AVG(revenue) split by clicked=1 minus clicked=0. The SELECT list and the ORDER BY do not share an expression.

---

### Break down the requirements

#### Step 1: Bucket by day-of-month

strftime('%d', impression_time) CAST to INTEGER so day keys sort numerically.

#### Step 2: Report avg and max overall

AVG(revenue) and MAX(revenue) across every row in the day bucket. These ignore the click flag.

#### Step 3: Rank by click premium

ORDER BY uses AVG(revenue) filtered to clicked=1 minus AVG filtered to clicked=0. LIMIT 1 picks the widest gap.

---

### The solution

**DAY WITH WIDEST CLICK PREMIUM**

```sql
SELECT
  CAST(strftime('%d', impression_time) AS INTEGER) AS day_of_month,
  AVG(revenue) AS avg_revenue,
  MAX(revenue) AS max_revenue
FROM ad_impressions
GROUP BY day_of_month
ORDER BY (
  AVG(CASE WHEN clicked = 1 THEN revenue END)
  - AVG(CASE WHEN clicked = 0 THEN revenue END)
) DESC
LIMIT 1
```

> **Cost Analysis**
>
> 300M rows, one full scan. The derived day key blocks index use, so plan on a hash aggregate over 31 buckets. Partition pruning only helps if a date filter is added.

> **Interviewers Watch For**
>
> That you noticed ORDER BY differs from SELECT, and that you used conditional AVG (not manual SUM/COUNT) so NULL handling on the ELSE branch is automatic.

> **Common Pitfall**
>
> Writing CASE WHEN clicked = 1 THEN revenue ELSE 0 END inside AVG counts non-clicks as zero-revenue clicks and tanks the numerator. ELSE NULL (or omit ELSE) is required.

> **The False Start**
>
> First instinct is to add the click-premium gap as a fourth SELECT column and ORDER BY 4. Reasonable, but the prompt only asks for three columns. Keep the gap inside ORDER BY.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you return the top three days? _(LIMIT 3, and decide if ties need RANK in a CTE.)_
- What if some days have zero clicks? _(AVG over an empty filtered set is NULL, the subtraction is NULL, and that day sorts last under DESC.)_
- How would you restrict this to one campaign? _(Add WHERE ad_campaign = ?. Same shape, smaller scan.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/best_day_for_ad_revenue)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.