Questions reported by candidates in 2024-2026 loops, paraphrased and de-identified. Each answer covers the approach, the gotcha, and the typical follow-up.
SQL · L4
Compute pin engagement funnel: impression to click to save to outbound click
Funnel SQL with conditional aggregation per stage. SUM CASE WHEN event_type = 'impression' THEN 1 END AS impressions, similar for click, save, outbound. Group by date or pin_id. Volunteer the de-duplication consideration: a single user impression-then-click should count once, not twice. The follow-up: how do you handle the user who saw the same pin 3 times before clicking? Answer: dedup by user_id and pin_id within the attribution window.
SQL · L4
Find boards with the highest engagement growth this week
Aggregate engagement events per board per week. LAG to previous week. Compute (current - prior) / NULLIF(prior, 0). Order by growth pct desc. Volunteer that new boards have artificially high growth (zero baseline); filter to boards with at least N engagements in trailing window.
SQL · L5
Compute pin similarity from board co-occurrence
Pins that appear together on boards are similar. Self-join pin_board on board_id, where pin_a.id < pin_b.id. Group by (pin_a, pin_b), count co-occurrences. Discuss why this is the cheap proxy for graph-based similarity, why it has noise from over-broad boards, and how Pinterest improves on it (board-quality weighting, content-based features).
SQL · L5
Attribute conversions to ad impressions within 28-day window
Self-join impression_events to conversion_events on user_id. WHERE conversion.ts BETWEEN impression.ts AND impression.ts + INTERVAL '28 days'. For multi-touch attribution, compute per-impression weight (last-touch: closest impression gets credit; linear: split equally; time-decay: weight by recency). Discuss how the choice affects advertiser incentives.
SQL · L5
Detect spam at scale: pinners with abnormal behavior
Rolling-window stats per pinner: pins per hour, repins per hour, follows per hour. Flag pinners with values > 3 std-dev above their cohort baseline. Discuss the bias: new pinners and viral content creators can produce false positives. Layer in: account age, content quality scores, IP-based clustering.
Python · L4
Find related pins via shared-board graph traversal
Build adjacency dict: for each pin, the set of boards it appears on. For each board, the set of pins. To find related to pin P: get P's boards, for each board get the pins, count overlap, return top N. O(N * avg_pins) per pin where N is pin count. Discuss why this won't scale to billions; mention precomputed neighbor table or embedding-based similarity for production.
def related_pins(pin_id: str, top_k: int = 50) -> list[tuple[str, int]]:
boards = pin_to_boards[pin_id]
candidates: dict[str, int] = {}
for board_id in boards:
for other_pin in board_to_pins[board_id]:
if other_pin == pin_id:
continue
candidates[other_pin] = candidates.get(other_pin, 0) + 1
# Top-k by co-occurrence count
return sorted(candidates.items(), key=lambda x: -x[1])[:top_k]Python · L4
Sessionize pin-engagement events with 30-min idle gap
Sort events by (user_id, ts). Walk events. Increment session_id when gap > 30 min OR user changes. State assumption: events with same ts are same session. Edge case: outbound-click events that bounce the user out of the app may not have a clear “session end” signal; document the choice.
Python · L5
Compute point-in-time features for training data
Given user click events as training labels and feature log as feature source, compute features as_of label_ts. Use pandas merge_asof or PySpark equivalent. Critical: every feature must have feature_ts <= label_ts to prevent leakage. Discuss why this matters: a model trained on leaked features looks great offline and breaks in production.
Python · L5
Implement ad-attribution with multi-touch weighting
Given conversion event and N impressions in the 28-day window, attribute credit per impression. Implement last-touch (1.0 to most recent), linear (1/N each), and time-decay (exponential weight by recency). Walk through a concrete 4-impression example with each method. Discuss the business trade-off: time-decay is more “fair” but harder for advertisers to reason about.
System Design · L5
Design the home feed recommendation feature pipeline
User events (clicks, saves, impressions) -> Kafka -> Flink (real-time features: last-N-clicked-categories, current-session signals, fresh interaction counts) -> Redis (online store, p99 < 10ms reads). Spark daily batch features (lifetime topic affinity, board diversity) -> S3 feature parquet -> registered in feature catalog (Galaxy). Online inference: ranker pulls features from Redis + lookup cache, calls ML model. Cover point-in-time correctness for training data, A/B test instrumentation, schema evolution as new features ship weekly.
User events -> Kafka (engagement_events topic, key=user_id)
-> Flink (real-time features, RocksDB state, EXACTLY_ONCE)
-> Redis (online store, 30-day TTL, p99 < 10ms)
-> S3 feature log (immutable, event-time partitioned)
Spark daily batch features:
S3 events -> Spark -> S3 feature parquet
-> Iceberg table for query
-> registered in Galaxy feature catalog
Training data:
Spark as_of_join between labels (next-day clicks) and feature
log, joined by (user_id, event_ts) where feature_ts <= label_ts.
Produces leak-free training data.
Online inference:
Ranker service reads features from Redis by user_id.
On Redis miss: fall back to default vectors.
Drift monitor: daily PSI / KS-test on feature distributions.System Design · L5
Design the ad attribution pipeline with 28-day click window
Two-track architecture. Real-time path: impressions and conversions to Kafka, Flink keyed by user_id maintains 28-day state of impressions, on each conversion emits attributed-impression record. Batch path: daily Spark job joins impressions and conversions across the same window, produces source-of-truth fact_attribution. Daily delta report comparing real-time to batch, alerting on drift > 0.5%. Cover: state size estimate (28 days * impression rate), key skew (whale users with heavy impression history), schema evolution as new ad formats ship.
System Design · L5
Design the trust-and-safety signal aggregation system
Reports of bad content (from users, automated detectors, external feeds) -> Kafka -> Flink (deduplicate, score, aggregate per content_id) -> serves to moderation console + automated takedown thresholds. Cover: signal weighting (user reports vs ML classifier vs external feed), audit log immutability, false-positive review workflow, appeals handling.
Modeling · L5
Design the schema for pin, board, user, topic graph
Three core fact tables. fact_pin: one row per pin, with pin_id, creator_user_id, created_ts, image_url, content_hash. fact_pin_board: one row per (pin_id, board_id), with added_ts, added_by_user_id (for repins). fact_board: one row per board, with board_id, owner_user_id, created_ts, topic_id. fact_user_topic_affinity: derived, one row per (user_id, topic_id, day) with affinity_score. Discuss: graph queries (related pins, related boards) are served from precomputed aggregate tables, not from joining the raw graph at query time.
Modeling · L5
Migrate from Hive to Iceberg without breaking downstream
Pinterest's real 2023 to 2024 migration. Approach: dual-write to Hive and Iceberg for 90 days, validate row counts and column-level checksums daily, switch readers to Iceberg one consumer at a time, deprecate Hive after all consumers cut over. Cover: schema differences (Iceberg hidden partitioning vs Hive explicit), file format choice (Parquet for both), file compaction strategy (Iceberg rewrite_data_files), and the cost spike during dual-write period.
Behavioral · L5
Tell me about a time you chose a simpler model over an elegant one
Pinterest culture rewards pragmatic decisions in ambiguous product contexts. Story should cover: the choice you faced, why the elegant option was tempting, why the simpler option was right (often: faster to ship, easier to debug, easier for the team to maintain), what the outcome was, and what you would tell someone facing the same choice. Decision postmortem essential.