SQL Log, Time-Window, And Graph Queries

What's being tested

These problems test relational data manipulation skills: deriving graph relationships from directed-edge tables and computing time-windowed event metrics for deliverability. Interviewers probe correct use of joins, window functions, deduplication, temporal joins, and robust aggregation for metric accuracy.

Patterns & templates

• SELF JOIN to find common neighbors: join edges e1 to edges e2 on e1.to = e2.to with e1.from <> e2.from, then GROUP BY and COUNT.

• Use ROW_NUMBER() OVER (PARTITION BY user ORDER BY ts DESC) to deduplicate event streams; filter row_number = 1 for last-event-per-(user,message).

• Temporal joins: inequality join ON a.user=b.user AND b.ts BETWEEN a.ts AND a.ts + interval to capture events in a window; watch inclusive/exclusive bounds.

• Compute rates with safe division: SUM(success) / NULLIF(SUM(attempts),0) to avoid divide-by-zero and report nulls meaningfully.

• Use COUNT(DISTINCT id) when uniqueness matters (unique recipients), otherwise duplicates inflate metrics.

• For mutual edges (friendship), require both (a->b) and (b->a) via self-join and EXISTS or INNER JOIN to enforce reciprocity.

• Index strategy for queries: indexes on (user, ts) and (from, to) speed joins; expect O(n log n) for sorting/window ops, linear for indexed lookups.

Common pitfalls

Pitfall: Double-counting — failing to dedupe message-level events (multiple opens for one message) inflates deliverability rates.

Pitfall: Direction confusion — treating directed edges as undirected when counting common friends yields incorrect reciprocity vs. common-neighbor answers.

Pitfall: Time-window boundaries — mixing inclusive/exclusive intervals or ignoring late-arriving events leads to off-by-one time-window errors.

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