SQL Analytics And Event Data Manipulation
Asked of: Data Scientist
Last updated

What's being tested
These problems test analytical SQL over event-level product data: deduplicating noisy logs, joining event and review tables, defining metric denominators, and computing time-windowed aggregates. Interviewers are checking whether you can turn ambiguous product questions into correct GROUP BY, window-function, and conditional-aggregation logic without double-counting users, flags, videos, or events.
Patterns & templates
-
Deduplication with
ROW_NUMBER() OVER (PARTITION BY idempotency_key ORDER BY event_ts, event_id); filterrn = 1before aggregating. -
Latest-status joins use
ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY reviewed_at DESC)to avoid many-to-one review duplication. -
Conditional aggregation with
COUNTIF(...),SUM(CASE WHEN ... THEN 1 ELSE 0 END), andCOUNT(DISTINCT CASE WHEN ... THEN user_id END). -
Metric ratios require explicit numerator and denominator CTEs; use
SAFE_DIVIDE,NULLIF, orCOALESCEfor zero-denominator cases. -
Time bucketing with
DATE_TRUNC(event_ts, MONTH)orDATE(event_ts); confirm timezone, inclusive/exclusive bounds, and event timestamp source. -
Ranking with
RANK,DENSE_RANK, orROW_NUMBER; specify tie behavior and deterministic secondary ordering likeORDER BY cnt DESC, product_id. -
MoM change with
LAG(metric) OVER (PARTITION BY country ORDER BY month)and formula(curr - prev) / prev; handle missing prior months.
Common pitfalls
Pitfall: Joining raw flags to raw reviews can multiply rows; aggregate or deduplicate each side to the intended grain before joining.
Pitfall:
COUNT(*)usually counts events, not users or entities; state whether the metric is event-level, user-level, video-level, or country-month-level.
Pitfall: Filtering after a
LEFT JOINin theWHEREclause can accidentally turn it into anINNER JOIN; put review-status filters inONor conditional aggregates.
Practice these
The practice cards below cover the canonical variants — solve all of them and time yourself.
Featured in interview prep guides
Practice questions
- Analyze video flags and reviews with SQLGoogle · Data Scientist · Take-home Project · Medium
- Write SQL/Python for messy event dataGoogle · Data Scientist · Technical Screen · Medium
- Find most co‑purchased product pairs in SQLGoogle · Data Scientist · Technical Screen · Medium
- Design a scalable video platform databaseGoogle · Data Scientist · Technical Screen · Medium
- Compute monthly CRR with merges and gapsGoogle · Data Scientist · Technical Screen · Medium
- Deduplicate events and rank products with SQLGoogle · Data Scientist · Technical Screen · Medium
- Compute violation rate and flag precision in SQLGoogle · Data Scientist · Onsite · Medium
- Calculate Top Countries' Gmail Usage and MoM ChangeGoogle · Data Scientist · Onsite · Medium
- Analyze User Flags and Review Outcomes for Moderation PrioritizationGoogle · Data Scientist · Technical Screen · Medium
- Design Scalable Database and Analyze E-commerce DataGoogle · Data Scientist · Technical Screen · Medium
Related concepts
- SQL AnalyticsData Manipulation (SQL/Python)
- SQL Event Log AnalyticsData Manipulation (SQL/Python)
- SQL Analytics Joins, Aggregations, And Windows
- SQL Analytical QueryingData Manipulation (SQL/Python)
- SQL Analytical Querying And Data ModelingData Manipulation (SQL/Python)
- SQL And Python Data ManipulationData Manipulation (SQL/Python)