Date Gaps vs Integer Gaps

Concepts covered: sqlLagLead

The LAG-plus-cumulative-SUM form is three CTEs instead of two. LAG exposes the predecessor's value. A CASE expression flags rows that start a new run. A cumulative SUM of the flag produces a run identifier. Each CTE is one job; the query reads top-down as the story of the calculation. The canonical sessionization query Reading the query top-down The lagged CTE adds a prev_event_time column via LAG; for the first event per user, prev_event_time is NULL. The flagged CTE adds a boolean is_new_session: 1 if there is no predecessor or if the gap from the predecessor exceeds thirty minutes, 0 otherwise. The sessions CTE adds a cumulative SUM of is_new_session, partitioned by user_id; this sum increments at every session boundary and stays constant within a session, producing a unique session_id

About This Interactive Section

This section is part of the Gaps and Islands: 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.