SQL Analytical Querying

What's being tested

Analytical querying for product data: turning raw event, user, and transaction tables into retention, revenue, ranking, overlap, and cohort metrics. Interviewers are probing whether you can write correct SQL/pandas under ambiguity: dedupe events, define time windows, handle ties, and explain metric edge cases.

Patterns & templates

• Window functions like ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts) dedupe or select first/last events; always add deterministic tie-breakers.

• Cohort joins for retention: build an anchor cohort, join future activity on user_id, constrain dates with BETWEEN, then aggregate by cohort day.

• Conditional aggregation with SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(DISTINCT CASE WHEN ... THEN user_id END) for segmented metrics.

• Top-N ranking uses RANK, DENSE_RANK, or ROW_NUMBER; choose based on tie behavior and state the business implication.

• Set overlap / Jaccard: dedupe item-user pairs, self-join by entity, compute $|A \cap B| / |A \cup B|$; avoid double-counting symmetric pairs.

• pandas groupby pipelines mirror SQL: drop_duplicates, groupby, agg, merge, rank, shift; watch index alignment and timezone-aware timestamps.

• Binary search over ordered logs is O(log n) probes when records are date-sorted; in SQL, compose bounded queries instead of scanning all dates.

Common pitfalls

Pitfall: Counting events instead of users will inflate retention, active-user, and conversion metrics when users generate multiple rows.

Pitfall: Using local calendar dates without clarifying UTC boundaries can shift next-day retention and revenue windows.

Pitfall: Forgetting tie semantics in top-category queries leads to inconsistent results; explicitly choose RANK, DENSE_RANK, or ROW_NUMBER.

Practice these

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