SQL Analytical Querying And Data Modeling

What's being tested

You’re being tested on analytical SQL for product and causal analysis: converting raw event, login, session, and panel tables into trustworthy user-level or time-level metrics. Interviewers look for clean use of joins, deduplication, window functions, temporal logic, and aggregation that supports Data Scientist decisions, not data-pipeline design.

Patterns & templates

• User/event aggregation with COUNT(DISTINCT user_id), SUM(CASE WHEN...), and GROUP BY date_trunc(...); define numerator, denominator, and grain first.

• Window functions like ROW_NUMBER(), LAG(), LEAD(), and RANK() OVER (PARTITION BY ... ORDER BY ...); always specify tie-breakers.

• Session and event sequencing by timestamp using LAG(event_ts) or LEAD(event_ts); watch time zones, missing events, and duplicate logs.

• Panel construction via user-date or user-week scaffolds using CROSS JOIN calendar tables; fill missing periods with COALESCE(..., 0).

• Causal-analysis prep for DID: create treated, post, and interaction terms; estimate effect as $(\Delta treated) - (\Delta control)$ after validating pre-trends.

• Cross-channel attribution using conditional distinct counts and set logic; decide whether users can belong to multiple channels or require mutually exclusive assignment.

• Efficient large-table SQL: filter early with WHERE, aggregate before joining, avoid accidental many-to-many joins, and inspect row counts after each CTE.

Common pitfalls

Pitfall: Counting events instead of users. If the metric is user proportion, use COUNT(DISTINCT user_id), not raw login rows.

Pitfall: Joining before deduplicating. A many-to-many join can silently inflate engagement, hours, or treatment effects.

Pitfall: Treating SQL output as final analysis. For DS work, explain assumptions, metric grain, cohort definitions, and validation checks.

Practice these

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