Feed Module Impressions, Clicks and CTR on Web Platforms Over a 7-Day Window
Company: Yahoo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
A news portal's homepage is made of content modules, and a daily traffic table records how often each article was shown and clicked in each module on each platform. Treat the two web platforms, `desktop_web` and `mobile_web`, together as the homepage product. For each module, report its total impressions, total clicks and click-through rate (CTR, defined as clicks divided by impressions) on those two platforms over the seven days from 2026-03-25 to 2026-03-31. Guard the CTR against division by zero, round it to 3 decimal places, and sort the modules by CTR from highest to lowest.
The report does not say what a module with zero impressions should show or how modules with equal CTR are ordered; this version fixes both in the Output Contract.
### 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 |
| `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.
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per module that has at least one web row in the window, with these columns, in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `module` | text | Module name |
| `impressions` | integer | Sum of `impressions` over the module's web rows in the window |
| `clicks` | integer | Sum of `clicks` over the same rows |
| `ctr` | numeric | The module's `clicks / impressions`, rounded to 3 decimal places; NULL when `impressions` is 0 |
Sort by `ctr` as returned (after rounding) descending, with NULL `ctr` last, then by `module` ascending. The result is empty when no web row falls in the window.
### Example
`feed_events_daily`:
| date | module | content_id | publisher_id | impressions | clicks | platform |
| --- | --- | --- | --- | --- | --- | --- |
| 2026-03-25 | top_stories | 3f8a2c91 | 4829 | 40000 | 1200 | desktop_web |
| 2026-03-31 | top_stories | 3f8a2c91 | 4829 | 10000 | 350 | mobile_web |
| 2026-03-28 | top_stories | 3f8a2c91 | 4829 | 30000 | 1500 | ios_app |
| 2026-03-24 | top_stories | 3f8a2c91 | 4829 | 20000 | 2000 | desktop_web |
| 2026-03-26 | need_to_know | 7b4e1d50 | 1173 | 23000 | 460 | mobile_web |
| 2026-03-27 | need_to_know | c19d0e72 | 4829 | 2000 | 320 | desktop_web |
| 2026-03-29 | content_stream | a2f9c834 | 7351 | 18000 | 900 | desktop_web |
| 2026-03-30 | content_stream | a2f9c834 | 7351 | 2000 | 100 | android_app |
| 2026-03-30 | breaking_news | e5b07a13 | 1173 | 0 | 0 | desktop_web |
| 2026-03-30 | trending | 9d2c4f60 | 7351 | 5000 | 400 | ios_app |
Expected result:
| module | impressions | clicks | ctr |
| --- | --- | --- | --- |
| content_stream | 18000 | 900 | 0.050 |
| need_to_know | 25000 | 780 | 0.031 |
| top_stories | 50000 | 1550 | 0.031 |
| breaking_news | 0 | 0 | NULL |
- `top_stories`: the desktop row on 2026-03-25 and the mobile row on 2026-03-31 give 50,000 impressions and 1,550 clicks, so the CTR is 0.031. The `ios_app` row is app traffic and the 2026-03-24 row is before the window, so neither counts.
- `need_to_know`: its two web rows give 25,000 impressions and 780 clicks; 0.0312 rounds to 0.031, tying `top_stories`, so `need_to_know` comes first by name.
- `content_stream`: only the `desktop_web` row counts, giving 900 / 18,000 = 0.050.
- `breaking_news`: its only web row has zero impressions, so it is returned with a NULL CTR and sorted last.
- `trending`: its only row in the window is on `ios_app`, so the module does not appear.
### Constraints and Clarifications
- No column contains NULL. `impressions` and `clicks` are non-negative, and `clicks` never exceeds `impressions` on a row.
- The window includes both end dates. Rows outside it, and rows on `ios_app` or `android_app`, are ignored.
- A module's CTR is its summed clicks divided by its summed impressions over both web platforms together, not an average of per-row or per-platform CTRs. It is a fraction such as 0.031, not a percentage.
- A module whose web rows in the window all have zero impressions is returned with `impressions` 0, `clicks` 0 and a NULL `ctr`. A module with no web rows in the window does not appear at all.
- Round with `numeric` arithmetic (half away from zero), and round only the final CTR.
- `module` and `platform` values are compared exactly as stored.
```hint Check the division
In PostgreSQL, dividing an integer by an integer returns an integer. Work out what 1550 / 50000 returns before you round it.
```
Overview: SQL practice on daily homepage traffic for a news portal: total impressions and clicks per feed module on the two web platforms over a seven-day window, plus a click-through rate. It tests date and platform filtering, aggregation, divide-by-zero handling, rounding and deterministic ordering in PostgreSQL.
Read the full Yahoo Data Scientist interview experience this question came from