Directional Pairs and Double-Counting

Concepts covered: sqlSelfJoin

The decision the interviewer is silently scoring Self-join is one of three tools for cross-row comparison. The others are window functions and recursive CTEs. Picking the right one for the shape of the comparison is the move. Defaulting to one without articulating why is the yellow flag. The interviewer is checking whether you treat the choice as a tradeoff or as a reflex. At Stripe in late 2022, a fraud detection query that should have been a sliding window comparison was written as an unconstrained self-join on transactions. The query ran fine on the staging dataset (a few million rows) and then crushed the production warehouse on its first scheduled run (two billion rows × two billion rows = unbounded shuffle, OOM'd every executor). The author was a strong engineer; the lesson the team

About This Interactive Section

This section is part of the Self-Join: Intermediate lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.