Yahoo Senior Data Scientist Interview Experience — Five Rounds, a Very Simple SQL Screen, No Offer

I started interviewing at the beginning of September. I've finished the onsite and didn't get an offer.

1. Round 1, Hiring Manager: we had a very pleasant chat.
2. Round 2, Technical Screening: the SQL below, very simple.
3. Round 3, Product Director: we talked about my past projects and about Yahoo.
4. Round 4, Engineering VP: we talked about how AI helps with work.
5. Round 5, Product VP: we talked about Yahoo's products.

The Technical Screening tables and questions:

-- Table 1: feed_events_daily
-- This is the audience traffic for Yahoo -- One row per (date x module x content_id x platform)
Table: feed_events_daily
| date       | module       | content_id | publisher_id | impressions | clicks | platform
| 2026-03-01 | top_stories  | 3f8a2c91   | 4829         | 45200       | 1356   | desktop_web
| 2026-03-01 | need_to_know | 7b4e1d50   | 1173         | 23100       | 462    | mobile_web
| 2026-03-01 | content_stream | a2f9c834 | 7351         | 18900       | 945    | ios_app
...
Modules: 'top_stories', 'need_to_know', 'content_stream', 'trending', 'breaking_news'
Platforms: 'desktop_web', 'mobile_web', 'ios_app', 'android_app'
Date range: 2026-01-01 to 2026-03-31 (90 days)

-- Table 2: content_meta
-- One row per content_id (no time dimension)
Table: content_meta
| content_id | title                            | category | publisher_id | publish_date |
| 3f8a2c91   | "Fed signals rate pause amid..." | finance  | 4829         | 2026-02-28   |
| 7b4e1d50   | "Championship game highlights"   | sports   | 1173         | 2026-03-01   |
| a2f9c834   | "New study links sleep to..."    | health   | 7351         | 2026-02-25   |
...
Categories: 'politics', 'sports', 'finance', 'health', 'entertainment', 'tech'

-- Table 3: publishers
-- Lookup, one row per publisher
Table: publishers
| publisher_id | publisher_name | publisher_tier | country |
| 4829         | Reuters        | premium        | US      |
| 1173         | ESPN           | premium        | US      |
| 7351         | Healthline     | standard       | US      |
...
Country: 'US', 'Canada', 'France'
Tiers: 'premium', 'standard', 'emerging'

--QUESTION 1
--Using feed_events_daily to calculate performance for each homepage module across both web platforms (desktop_web and mobile_web) over the 7 day period (2026-03-25 to 2026-03-31). For simplicity sake, assume that filtering these two platforms captures the Yahoo homepage product.
-- We would want impression, clicks and ctr for each module
--Order by CTR descending (CTR defined as clicks/impressions)
--For CTR, Make sure that you account for possible divide by zero errors and round to 3 decimal places

--QUESTION 2
--Now, find the top 3 articles by CTR within each module (across all platforms - include web and app).
-- Include the impression & clicks per each of these articles but include articles with only at least 1,000 total impressions.
--Assume same time period as Question 1 (2026-03-25 to 2026-03-31)
--Sort by module and rank accordingly

--QUESTION 3 - Publisher Level Metrics
--From a publisher level, pull the number of articles, total impressions, total clicks and publisher ctr
--Also break out CTR separately for App (ios_app and android_app) vs Web (desktop_web and mobile_web)
--Only include US publishers for audience traffic engagement for March 2026.
-- Order by publisher CTR descending.

--QUESTION 4 - Publisher CTR vs Average Across All
--Now compare each publisher's CTR against the simple average CTR across all publishers (treat each publisher as one row - not weighted by impression volume).
--Order by ctr_vs_avg ascending (worst performers first).