SQL Analytics And Event Data Manipulation

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); filter rn = 1 before 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), and COUNT(DISTINCT CASE WHEN ... THEN user_id END).

• Metric ratios require explicit numerator and denominator CTEs; use SAFE_DIVIDE, NULLIF, or COALESCE for zero-denominator cases.

• Time bucketing with DATE_TRUNC(event_ts, MONTH) or DATE(event_ts); confirm timezone, inclusive/exclusive bounds, and event timestamp source.

• Ranking with RANK, DENSE_RANK, or ROW_NUMBER; specify tie behavior and deterministic secondary ordering like ORDER 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 JOIN in the WHERE clause can accidentally turn it into an INNER JOIN; put review-status filters in ON or conditional aggregates.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.