Publisher CTR with App vs Web Split Compared to the Unweighted Publisher Average
Company: Yahoo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
A news portal records daily traffic for every article it shows on its homepage feed, and each article belongs to a publisher. Build a publisher-level report for March 2026 (2026-03-01 to 2026-03-31) covering only publishers based in the US. For each publisher, report the number of articles, total impressions, total clicks and overall click-through rate (CTR, defined as clicks divided by impressions), plus CTR broken out separately for app traffic (`ios_app` and `android_app`) and web traffic (`desktop_web` and `mobile_web`). Then compare each publisher's CTR with the simple average CTR across the publishers in the report, where each publisher counts once regardless of its impression volume, and list the worst performers first.
The report asks for the publisher table first, ordered by publisher CTR descending, and then for the comparison as a follow-up ordered by `ctr_vs_avg` ascending. This version returns both in one result, in the follow-up's order. The report does not define `ctr_vs_avg`, the rounding of these columns, or which publishers the average covers; this version uses the difference from the average, rounds every CTR to 3 decimal places, and averages over the publishers in this report. The screen also provided an article metadata table (title, category, publisher, publish date), which this question does not need.
### Input Table(s)
**`feed_events_daily`**: daily audience traffic, one row per (`date`, `module`, `content_id`, `platform`).
| Column | Type | Meaning |
| --- | --- | --- |
| `date` | DATE | Calendar date of the traffic |
| `module` | TEXT | Homepage module: `top_stories`, `need_to_know`, `content_stream`, `trending` or `breaking_news` |
| `content_id` | TEXT | Article identifier, a lowercase hexadecimal string such as `3f8a2c91` |
| `publisher_id` | INTEGER | Publisher of the article; references `publishers.publisher_id` |
| `impressions` | INTEGER | Times the article was shown in that module on that platform that day |
| `clicks` | INTEGER | Clicks on the article in that module on that platform that day |
| `platform` | TEXT | `desktop_web`, `mobile_web`, `ios_app` or `android_app` |
Primary key: (`date`, `module`, `content_id`, `platform`). The table covers 2026-01-01 to 2026-03-31.
**`publishers`**: lookup table, one row per publisher.
| Column | Type | Meaning |
| --- | --- | --- |
| `publisher_id` | INTEGER | Primary key |
| `publisher_name` | TEXT | Display name |
| `publisher_tier` | TEXT | `premium`, `standard` or `emerging` |
| `country` | TEXT | `US`, `Canada` or `France` |
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per publisher whose `country` is `US` and that has at least one `feed_events_daily` row dated in March 2026, with these columns, in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `publisher_id` | integer | Publisher identifier |
| `publisher_name` | text | Publisher name |
| `num_articles` | integer | Number of distinct `content_id` values among the publisher's March rows |
| `impressions` | integer | Sum of `impressions` over the publisher's March rows, all modules and platforms |
| `clicks` | integer | Sum of `clicks` over the same rows |
| `publisher_ctr` | numeric | `clicks / impressions` over those rows, rounded to 3 decimal places; NULL when `impressions` is 0 |
| `app_ctr` | numeric | The same ratio over the publisher's March `ios_app` and `android_app` rows; NULL when there are no such rows or their impressions sum to 0 |
| `web_ctr` | numeric | The same ratio over the publisher's March `desktop_web` and `mobile_web` rows; NULL when there are no such rows or their impressions sum to 0 |
| `avg_publisher_ctr` | numeric | Unweighted mean of the unrounded publisher CTRs of every publisher in the result whose CTR is not NULL, rounded to 3 decimal places; the same value on every row, NULL if no publisher has a CTR |
| `ctr_vs_avg` | numeric | Unrounded publisher CTR minus the unrounded average, rounded to 3 decimal places; NULL when the publisher's CTR is NULL |
Sort by `ctr_vs_avg` as returned (after rounding) ascending, with NULL `ctr_vs_avg` last, then by `publisher_id` ascending. The result is empty when no US publisher has March traffic.
### Example
`publishers`:
| publisher_id | publisher_name | publisher_tier | country |
| --- | --- | --- | --- |
| 4829 | North Wire | premium | US |
| 1173 | Field Sports Daily | premium | US |
| 7351 | Well Read Health | standard | US |
| 2604 | Maple Ledger | standard | Canada |
| 3390 | Tech Sprout | emerging | US |
| 5512 | Quiet Gazette | emerging | US |
`feed_events_daily`:
| date | module | content_id | publisher_id | impressions | clicks | platform |
| --- | --- | --- | --- | --- | --- | --- |
| 2026-03-02 | top_stories | 3f8a2c91 | 4829 | 10000 | 300 | desktop_web |
| 2026-03-02 | top_stories | 3f8a2c91 | 4829 | 10000 | 500 | ios_app |
| 2026-03-15 | trending | c19d0e72 | 4829 | 5000 | 200 | mobile_web |
| 2026-02-28 | top_stories | 3f8a2c91 | 4829 | 8000 | 800 | desktop_web |
| 2026-03-10 | need_to_know | 7b4e1d50 | 1173 | 20000 | 400 | mobile_web |
| 2026-03-11 | need_to_know | 7b4e1d50 | 1173 | 0 | 0 | android_app |
| 2026-03-20 | content_stream | a2f9c834 | 7351 | 6000 | 180 | ios_app |
| 2026-03-21 | content_stream | d4e5f6a7 | 7351 | 4000 | 120 | android_app |
| 2026-03-05 | top_stories | b8c9d0e1 | 2604 | 9000 | 900 | desktop_web |
| 2026-03-31 | breaking_news | f0e1d2c3 | 3390 | 0 | 0 | mobile_web |
| 2026-01-15 | trending | 0a1b2c3d | 5512 | 3000 | 90 | desktop_web |
Expected result:
| publisher_id | publisher_name | num_articles | impressions | clicks | publisher_ctr | app_ctr | web_ctr | avg_publisher_ctr | ctr_vs_avg |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 1173 | Field Sports Daily | 1 | 20000 | 400 | 0.020 | NULL | 0.020 | 0.030 | -0.010 |
| 7351 | Well Read Health | 2 | 10000 | 300 | 0.030 | 0.030 | NULL | 0.030 | 0.000 |
| 4829 | North Wire | 2 | 25000 | 1000 | 0.040 | 0.050 | 0.033 | 0.030 | 0.010 |
| 3390 | Tech Sprout | 1 | 0 | 0 | NULL | NULL | NULL | 0.030 | NULL |
- Publisher 4829: the 2026-02-28 row is outside March. Its March rows cover two articles (`3f8a2c91` on two rows and `c19d0e72` on one), with 25,000 impressions and 1,000 clicks, a CTR of 0.040. App: 500 / 10,000 = 0.050. Web: 500 / 15,000 is about 0.0333, which rounds to 0.033.
- Publisher 1173: 20,000 impressions and 400 clicks, a CTR of 0.020. Its only app row has zero impressions, so `app_ctr` is NULL.
- Publisher 7351: 10,000 impressions and 300 clicks, all on apps, so the CTR and `app_ctr` are 0.030 and `web_ctr` is NULL because it has no web rows.
- Publisher 3390: a US publisher with March traffic, so it appears, but its only row has zero impressions. Every CTR is NULL, it is left out of the average, and it sorts last.
- Publisher 2604 is in Canada and publisher 5512 has no March traffic, so neither appears.
- The average is (0.040 + 0.020 + 0.030) / 3 = 0.030. The impression-weighted CTR, 1,700 / 55,000 (about 0.031), is not what is asked, and neither is an average that counts publisher 3390 as zero.
### Constraints and Clarifications
- No column in either table contains NULL. `impressions` and `clicks` are non-negative, and `clicks` never exceeds `impressions` on a row. Every `publisher_id` in `feed_events_daily` exists in `publishers`.
- March 2026 means dates from 2026-03-01 to 2026-03-31 inclusive. All five modules count toward every total.
- Attribute traffic to publishers with `feed_events_daily.publisher_id`. "US publishers" means `country = 'US'`, compared exactly as stored.
- `num_articles` counts each article once per publisher, however many dates, modules or platforms it appears on, including rows with zero impressions.
- Each CTR is summed clicks divided by summed impressions over the relevant rows, not an average of per-row CTRs. CTRs are fractions such as 0.040, not percentages.
- The average and the difference use unrounded CTRs; round only the output columns, with `numeric` arithmetic (half away from zero).
```hint One number on every row
The comparison needs a value computed across all of the finished publisher rows but shown on each of them. Decide at what point in the query that value can exist.
```
Overview: Publisher-level SQL reporting on news feed traffic for US publishers in March 2026: count articles, total impressions and clicks, and compute overall, app and web click-through rates. The follow-up compares each publisher's CTR with the unweighted average across publishers and lists the worst performers first.
Read the full Yahoo Data Scientist interview experience this question came from