Practical Analytics And SQL Fluency

What's being tested

Demonstrates practical SQL metric translation: turn a product question into correct, auditable queries that answer “who,” “when,” and “how much.” Interviewers check clarity on deduplication, time-windowing, cohort/funnel logic, and sensible performance tradeoffs for large tables.

Patterns & templates

• Last-event-per-entity using ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) to dedupe; filter row_num = 1; watch ties.

• Session / funnel counts: event-level COUNT(*) then COUNT(DISTINCT user_id) per step; use CASE WHEN + GROUP BY for step conversion rates.

• Rolling/lag analysis with LAG(col) OVER (PARTITION BY user_id ORDER BY ts) to compute time-between-events or churn triggers.

• Time bucketing via DATE_TRUNC('day', ts) or TIMESTAMP_TRUNC for DAU/WAU/MAU; be explicit about timezone conversion.

• Cohort retention: cohort by MIN(event_date), then LEFT JOIN back on users to compute week N retention; prefer CTEs (WITH) for readability.

• **Avoid SELECT *** on wide tables; filter early with indexed predicates and limit scan range (WHERE ts BETWEEN), or sample (TABLESAMPLE) for prototyping.

• Use COUNT(DISTINCT ...) sparingly — it's correct but expensive at scale; consider approximate functions (APPROX_COUNT_DISTINCT) if acceptable.

Common pitfalls

Pitfall: Double-counting by grouping on event_id instead of user_id leads to inflated engagement metrics; always validate numerator/denominator entity alignment.

Pitfall: Using local timestamps without normalizing causes off-by-one-day cohort errors across timezones; declare and apply a canonical timezone.

Practice these

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