SQL Product Analytics

What's being tested

These problems test SQL product analytics: turning raw event tables into trustworthy metrics such as conversion, retention, CTR, frequency, revenue, and cohort activity. Interviewers are probing whether you can reason about event attribution, deduplication, time windows, and safe aggregation without double-counting users, impressions, clicks, or sessions.

Patterns & templates

• Time-based attribution joins — join events where conversion_ts >= assignment_ts and within a defined window; prefer earliest valid assignment using ROW_NUMBER.

• Deduplication before aggregation — use ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY event_ts) or COUNT(DISTINCT ...) when raw logs contain repeats.

• Cohort retention template — define cohort date with MIN(activity_date), join future activity, then compute D1, D7, or D30 retention by cohort.

• Session-level aggregation — group impressions, ads, clicks, or calls by user_id, session_id; compute per-session metrics before rolling up to users.

• Null-safe metric formulas — use COALESCE, NULLIF, and CASE WHEN; CTR = clicks / NULLIF(impressions, 0) avoids divide-by-zero failures.

• Window functions for ranking and percentiles — use ROW_NUMBER, LAG, LEAD, PERCENTILE_CONT, or NTILE for recency, sequencing, and distribution questions.

• Geo/date dimensional joins — join user or event dimensions carefully; decide whether geography and date are attributed at event time, user profile time, or UTC date.

Common pitfalls

Pitfall: Joining impressions to clicks only on user_id creates many-to-many inflation; include impression_id, ad_id, or a strict time window.

Pitfall: Computing retention from all active users instead of the original cohort changes the denominator and overstates product health.

Pitfall: Filtering soft-deleted or invalid records after aggregation can leave deleted events inside counts; filter eligible rows in the base CTE.