Top 3 Articles by CTR in Each Feed Module with a 1,000-Impression Floor
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. For the seven days from 2026-03-25 to 2026-03-31, find the three articles with the highest click-through rate (CTR, defined as clicks divided by impressions) within each module, counting traffic from all four platforms, web and app. Only articles with at least 1,000 total impressions are eligible. Return each of these articles' impressions, clicks and CTR, sorted by module and then by rank.
The report does not say how ties in CTR are handled or whether the 1,000-impression floor is measured within one module or across modules; this version fixes both below.
### 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 up to three rows per module, with these columns, in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `module` | text | Module name |
| `article_rank` | integer | 1, 2 or 3: the article's position within the module |
| `content_id` | text | Article identifier |
| `impressions` | integer | The article's total impressions in this module over the window, all platforms |
| `clicks` | integer | The article's total clicks in this module over the same rows |
| `ctr` | numeric | `clicks / impressions`, rounded to 3 decimal places |
Ranking rules:
- An article's totals are summed per (`module`, `content_id`) over every platform and every date in the window. An article that appears in several modules is measured and ranked separately in each.
- An article is eligible in a module when its total impressions there are at least 1,000.
- Within each module, rank the eligible articles by unrounded CTR descending, breaking ties by `content_id` ascending, and number them 1, 2, 3, and so on with no gaps or repeats. Return ranks 1 to 3 only.
Sort by `module` ascending, then `article_rank` ascending.
### Example
`feed_events_daily`:
| date | module | content_id | publisher_id | impressions | clicks | platform |
| --- | --- | --- | --- | --- | --- | --- |
| 2026-03-25 | top_stories | 3f8a2c91 | 4829 | 3000 | 90 | desktop_web |
| 2026-03-26 | top_stories | 3f8a2c91 | 4829 | 1000 | 50 | ios_app |
| 2026-03-27 | top_stories | 5c6d7e8f | 1173 | 1200 | 48 | mobile_web |
| 2026-03-28 | top_stories | 1a2b3c4d | 7351 | 800 | 40 | android_app |
| 2026-03-29 | top_stories | 1a2b3c4d | 7351 | 400 | 8 | desktop_web |
| 2026-03-30 | top_stories | 9e8d7c6b | 4829 | 900 | 120 | ios_app |
| 2026-03-24 | top_stories | 9e8d7c6b | 4829 | 5000 | 500 | desktop_web |
| 2026-03-31 | top_stories | 7b4e1d50 | 1173 | 2000 | 60 | mobile_web |
| 2026-03-26 | need_to_know | 7b4e1d50 | 1173 | 5000 | 100 | mobile_web |
| 2026-03-27 | need_to_know | 7b4e1d50 | 1173 | 5000 | 150 | android_app |
| 2026-03-28 | need_to_know | c19d0e72 | 4829 | 1000 | 21 | desktop_web |
| 2026-03-29 | content_stream | a2f9c834 | 7351 | 999 | 80 | ios_app |
Expected result:
| module | article_rank | content_id | impressions | clicks | ctr |
| --- | --- | --- | --- | --- | --- |
| need_to_know | 1 | 7b4e1d50 | 10000 | 250 | 0.025 |
| need_to_know | 2 | c19d0e72 | 1000 | 21 | 0.021 |
| top_stories | 1 | 1a2b3c4d | 1200 | 48 | 0.040 |
| top_stories | 2 | 5c6d7e8f | 1200 | 48 | 0.040 |
| top_stories | 3 | 3f8a2c91 | 4000 | 140 | 0.035 |
- `top_stories`: `9e8d7c6b` has the highest CTR in the module, but only its 900 impressions on 2026-03-30 fall in the window (the 2026-03-24 row is before it), so it is not eligible. `1a2b3c4d` sums an app row and a web row to 1,200 impressions and 48 clicks, the same CTR of 0.040 as `5c6d7e8f`, so the smaller `content_id` ranks first. `3f8a2c91` (4,000 impressions and 140 clicks across desktop and iOS, 0.035) is third, and `7b4e1d50` (0.030) is fourth and left out.
- `need_to_know`: `7b4e1d50` is measured again on its traffic in this module alone: 10,000 impressions and 250 clicks, a CTR of 0.025. `c19d0e72` has exactly 1,000 impressions, so it is eligible. The module has only two eligible articles, so it returns two rows.
- `content_stream`: its only article has 999 impressions, so the module returns no rows.
### 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 are ignored. All four platforms count.
- The 1,000-impression floor and the CTR both use the article's totals within one module across all platforms and dates in the window, never a single row. CTR is summed clicks divided by summed impressions, a fraction such as 0.035, not a percentage.
- Every eligible article has at least 1,000 impressions, so no CTR divides by zero.
- A module with fewer than three eligible articles returns all of them; a module with none does not appear.
- Rank on the unrounded CTR; round only the output `ctr`, with `numeric` arithmetic (half away from zero).
- `content_id` values are lowercase hexadecimal strings compared as text, so their ascending order does not depend on collation.
```hint Order of operations
Before writing any ranking logic, write out what one row per article and module should look like, and which of those rows are still in the running.
```
Overview: A PostgreSQL ranking exercise on daily feed-module traffic: within each homepage module, find the three articles with the highest click-through rate over a seven-day window, counting both web and app platforms. Only articles with at least 1,000 impressions qualify, and CTR ties must be broken deterministically.
Read the full Yahoo Data Scientist interview experience this question came from