DoorDash Data Engineer Interview Preparation Guide (Entry Level)

At real enterprise scale, the general lakehouse-versus-managed-warehouse trade-off sharpens around three things specifically: how concurrency actually behaves under load, what Atomicity, Consistency, Isolation, Durability (ACID) guarantees really mean for complex, high-volume writes, and how well each side handles mixed streaming-plus-batch ingestion without extra engineering effort.

What actually changes at scale

Concurrency. At moderate scale, both a lakehouse and a managed warehouse can serve many concurrent business intelligence (BI) users acceptably. At enterprise scale, a managed warehouse's concurrency handling (workload isolation, automatic scaling of independent compute clusters) tends to be more turnkey: the vendor has already solved the noisy-neighbor problem. A lakehouse can match this, but it usually requires deliberate compute-pool separation and tuning that the team has to design and maintain, rather than getting it largely for free from the platform.

Atomicity, consistency, isolation, and durability guarantees. Open table formats provide table-level ACID guarantees, which is a real and important upgrade over plain files. At enterprise scale, though, the volume and concurrency of writers (many pipelines committing to the same tables simultaneously) stresses that guarantee harder: metadata operations that are effortless with a handful of writers can become a real bottleneck with hundreds, and this is a genuine engineering problem a lakehouse team has to actively manage. A managed warehouse's transactional model is generally simpler to reason about (its query engine and storage are one integrated system), which is part of why it's easier to operate at scale with less specialized tuning, even though it offers less flexibility for complex multi-writer merge patterns.

Mixed streaming-plus-batch ingestion. A lakehouse's storage model is a natural fit for combining continuous streaming writes with periodic large batch loads into the same tables, because both are just writers committing to the same underlying table format. Managed warehouses have added streaming ingestion paths as well, but historically their strength was batch-oriented loading, so mixing a high-volume continuous stream with large batch jobs against the same warehouse tables is more likely to need careful workload management to avoid one interfering with the other.

Worked example

Consider an enterprise ingesting both a continuous stream of transaction events and nightly batch corrections from a legacy system into the same customer-activity table, served to hundreds of concurrent BI analysts. On a lakehouse, this is architecturally natural (both are writers against an ACID table), but the team needs mature compaction and metadata management to keep query performance from degrading as writer volume grows. On a managed warehouse, the batch and streaming paths might need to be more explicitly separated and reconciled, but the query-serving side to hundreds of concurrent analysts is more likely to just work at that scale without as much bespoke tuning.

Trade-offs and pitfalls

The pitfall at this scale is picking a side based on the same general trade-offs that applied at moderate scale, and being surprised when metadata scaling, writer concurrency, or workload isolation becomes the actual bottleneck rather than storage cost or basic query speed. Enterprise-scale lakehouse deployments succeed when the team genuinely invests in the operational discipline (compaction schedules, metadata monitoring, workload-isolated compute) the pattern requires; they struggle when it's adopted purely for the cost story without that investment.

Situation: At my previous company I was leading a cross-functional effort to build a real-time “feature analytics” product: APIs and dashboards that exposed per-feature usage metrics to product teams. After six months of work (Spark streaming pipelines, a Redshift data mart, and a React dashboard) early user interviews were positive, but usage metrics lagged and operational costs were rising.

Task: I had to decide whether to keep investing in product-facing features or pivot the effort so engineering teams could get more value (and we could reduce cost) by focusing on a lightweight, standardized observability platform for existing pipelines.

Action:

• I gathered quantitative evidence: usage logs for the dashboard, frequency of API calls, time-to-insight measured by tickets, and cost by AWS service. Dashboard DAU was <4% of intended users; API calls were concentrated to 2 teams. Operational cost projections showed a 2.5x increase if we scaled.
• I ran qualitative tests: 8 targeted usability sessions and a short survey to understand why teams weren’t adopting the product. Feedback showed teams preferred integrating metrics into their existing tooling and wanted lineage/alerting more than dashboards.
• I formed a rapid experiment: build a minimal platform layer (Kafka + lightweight metrics topic + standardized schema + small-scope lineage service) and ship connectors to two high-value teams. We instrumented adoption via integration count, mean time to detect (MTTD) data issues, and number of manual tickets closed.
• I presented a decision memo and roadmap to stakeholders showing evidence, experiments, and recommended pivot. I negotiated scope and reallocated two engineers to the platform MVP.

Result: Within 10 weeks the platform connectors reduced MTTD by 40% for pilot teams and eliminated 60% of the manual data-quality tickets those teams opened. Dashboard usage didn’t materially increase, validating our decision. Cost projections for the platform were 35% lower than continuing the original product roadmap. The company adopted the platform approach; I transitioned the remaining product work into a set of optional integrations.

Learnings: I learned to prioritize data-driven, low-friction integration over standalone product features for platform users. Testing assumptions with small pilots, combining quantitative and qualitative data, and presenting clear trade-offs made the pivot low-risk and accepted by stakeholders.

Direct answer: Reach for a window function whenever the downstream consumer needs to see every original row and a group-level or ordered fact attached to it. GROUP BY collapses N rows into one summary row per group, which is correct when you only need the summary, but it permanently discards which specific rows produced that summary, their order relative to each other, and any row that isn't itself the aggregate result. A window function computes the same kind of aggregate or positional fact but writes it back onto every original row instead of replacing them.

Structured elaboration

Situation 1: rolling or trailing time-window features. You need each user-day's trailing 30-day spend as a feature, but you also need to keep the original daily row (date, that day's own spend, any other daily attributes) for model training or a time-series chart. GROUP BY user would collapse all of a user's days into one row and lose the day-by-day granularity entirely; you could GROUP BY user, day, but that just gives you the single day's own spend back, with no way to see the trailing context on that same row.

SELECT user_id, day, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY day
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS spend_30d
FROM transactions;

What GROUP BY would throw away: every day's individual spend value and its position relative to neighboring days; you'd be left with only a single number per user, not a sequence.

Situation 2: row-to-row comparisons (LAG/LEAD). You need the time since a customer's previous transaction, or the previous transaction's amount, as a feature on the current transaction's row.

SELECT *,
  LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS prev_ts,
  ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS time_since_prev
FROM events;

What GROUP BY would throw away: there is no group-level aggregate that captures "the specific previous row's value" at all; MIN/MAX/AVG summarize across a whole group, they cannot single out "the one row immediately before this one." GROUP BY has no concept of adjacency between rows.

Situation 3: ranking or flagging within a group, kept at row grain. You need to flag each purchase as a customer's first purchase, or rank each purchase by amount within that customer's history, while keeping every purchase row intact for a transaction log or funnel report.

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 AS is_first_purchase,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS amount_rank
FROM purchases;

What GROUP BY would throw away: the identity of which specific row is the first, or how each row ranks against its peers; GROUP BY user_id with MIN(ts) tells you when the first purchase happened but not which purchase row that was or anything else about it (amount, product, channel), because those attributes belonged to a row that GROUP BY has already discarded.

Key points

• The unifying idea: window functions annotate rows, GROUP BY replaces rows with a summary.
• Whenever "I need this detail row, plus some fact computed across its group or neighbors, on the same output row," that's the window-function signal.
• Deduplication and top-N-per-group patterns follow the same shape: rank within a partition, then filter, rather than aggregating the group away.

Worked example

Fixture for events (situation 2's row-to-row comparison):

Running the LAG query above against this data (verified in DuckDB):

Each user's opening row has no predecessor, so prev_ts and time_since_prev come back NULL; every later row carries the previous row's timestamp and the gap since it, and all five rows stay in the output, rather than being collapsed down to two rows (one per user) the way a GROUP BY user_id would.

Trade-offs & pitfalls

Prefer GROUP BY plus a join back to detail rows over a window function when the aggregate itself is expensive or when only a small number of pre-aggregated values are actually needed downstream (e.g., a small lookup table of category totals used by many queries): materializing the aggregate once and joining is often clearer and cheaper than recomputing a window function over the full detail set every time the query runs. A common wrong turn is reaching for a window function purely out of habit when a true GROUP BY summary was all the report ever needed; that adds unnecessary per-row computation and a wider result set for no benefit.

Direct answer

For a dashboard that needs a 10-minute refresh and serves thousands of users, I'd pick option (A), pure batch with 10-minute micro-batches, because the freshness bar is well within micro-batch's comfortable range and it avoids paying for always-on streaming infrastructure that this workload doesn't actually need; I'd reserve (B) or (C) for a tighter latency bar or a genuinely mixed-freshness requirement.

Structured elaboration

(A) Pure batch, 10-minute micro-batches: cost is the lowest of the three, since compute only runs for the duration of each micro-batch job, not continuously. Latency is bounded by the batch interval plus run time, comfortably meeting a 10-minute bar if the job itself takes a couple of minutes. Complexity is the lowest: standard scheduled-job tooling, straightforward failure recovery (rerun the batch). Operational burden is light: no stream-processing expertise required on the team, no state-management or watermark tuning.

(B) Streaming engine: cost is higher (always-on compute and, likely, resident state), for a latency improvement (sub-minute) the stated requirement doesn't ask for. Complexity and operational burden both rise meaningfully: the team now owns checkpointing, backpressure, and event-time semantics for a workload that didn't need sub-10-minute freshness in the first place. This option only pays for itself if the 10-minute figure understates the real requirement (see the follow-up question) or is expected to tighten soon.

(C) Hybrid, CDC plus periodic batch backfills: cost sits between the other two: you pay for a CDC pipeline (log-based change capture, generally lighter than a full stream processor since it only ships committed changes) on top of the existing batch job, so it's more than (A) alone but usually less than running (B) for every metric. Latency is genuinely mixed rather than a single number: the CDC-fed subset of metrics gets near-real-time freshness (seconds to low minutes), while everything else stays on the 10-minute batch cadence. Complexity rises meaningfully over (A): there are now two data paths to build, test, and reason about, and consumers have to know which panel is on which path. Operational burden follows complexity: the team owns both the batch scheduler and the CDC pipeline's health (replication lag, connector failures), more monitoring surface than (A) alone, though usually less than operating a full stream processor the way (B) requires. This option is worth its cost only if some subset of the dashboard's metrics genuinely need faster-than-10-minute updates (say, an incident-monitoring panel embedded in an otherwise 10-minute dashboard); if the entire dashboard genuinely only needs 10 minutes uniformly, (C) adds this cost and complexity without a matching benefit.

Worked example

An internal ops dashboard for a logistics company needs shipment-status counts refreshed every 10 minutes for thousands of warehouse staff to check periodically. Option (A): a Spark or warehouse-native job runs every 10 minutes, reads the latest partition, recomputes the aggregates, and writes to a serving table; total infrastructure is a scheduler and a compute cluster that only runs a few minutes out of every ten. This comfortably clears the 10-minute bar with meaningful margin, at a fraction of what an always-on Flink cluster serving the same numbers would cost, since the compute is idle (and unbilled, in a serverless/ephemeral setup) most of the time.

Trade-offs and pitfalls

The recurring mistake on this kind of question is picking (B) because streaming is the more sophisticated-sounding answer, without checking whether the stated 10-minute requirement is actually the real one; always confirm whether "10 minutes" is a hard business requirement or a starting ask that will tighten soon, since building (A) now and having to redo the architecture in six months is a real cost too, just a deferred one. The other mistake is reaching for (C) reflexively whenever a requirement mentions "near-real-time," without confirming that only a genuinely distinct subset of metrics needs the faster path; if the whole dashboard shares one freshness requirement, hybrid adds complexity without adding value.

Direct answer. For a long, dynamic list of IDs, stage the list into a temporary table (or, where the engine supports it, pass it as a single array/table-valued parameter) and join against that, rather than embedding thousands of literal values directly inside an IN (...) clause; this avoids both the query-text bloat and the plan-caching problems a huge literal IN-list causes.

Structured elaboration. Embedding thousands of literal values directly in the SQL text makes the query itself enormous, which costs real parsing and planning time on every execution, and, because the exact literal values differ every time, defeats plan caching entirely, forcing a fresh, expensive compile for what is structurally the same query shape every single time. Staging the list into a temporary table instead keeps the query TEXT small and stable (a join against a table, structurally identical regardless of how many IDs are in the list), which restores plan reuse, and lets the engine apply normal join optimization (an index on the temp table, if it's large enough to be worth one) rather than the special-cased handling many engines apply to a giant literal IN-list.

Worked example. I verified the join-based approach functionally: filtering a 100,000-row items table down to 715 matching rows using a staged temp table of about 715 wanted IDs, joined rather than IN-listed, returned exactly the expected count, confirming the semantics are equivalent to an IN-list of the same values.

CREATE TEMP TABLE wanted_ids (id INT);
-- populate wanted_ids with the caller-supplied list (bulk insert, not thousands of literals inline)

SELECT i.* FROM items i
JOIN wanted_ids w ON i.id = w.id;

This avoids both a multi-thousand-value literal list in the query text and the plan-cache churn that comes with it, while returning identical results to the equivalent (much larger and slower to plan) WHERE id IN (...) form.

Trade-offs and pitfalls. Staging into a temp table costs an extra round trip (create, populate, then query) compared to a single inline query, which is worth it once the list is large enough that plan-compile and query-text overhead dominate; for a genuinely short, stable list (a handful of values), a plain IN-list remains simpler and perfectly fine, this fix specifically targets the case where the list can grow into the thousands.

Complexity

A literal IN-list of size N adds roughly O(N) to both query-text size and (for engines that don't specialize this) plan-compile time on every execution; staging into a temp table keeps the query's own compile cost independent of N, paying instead a one-time O(N) cost to populate the temp table.

Edge cases

If the ID list can contain duplicates and the downstream join isn't meant to multiply matching rows, deduplicate the staged list (or use a DISTINCT/semi-join form) before joining, since a duplicate in the staged table WILL produce a duplicate joined row where a plain IN-list wouldn't have.

Situation: A P0 incident is degrading dashboards; product leadership proposes a risky hotfix that may corrupt historical data but could restore dashboards within an hour.

Decision framework I would use:

1. Rapid triage (first 10–15 mins)

• Confirm root-symptom mapping with SRE/engineering on-call and data pipeline owner: is the outage read-only dashboards or underlying ETL failure?
• Identify the exact systems and data artifacts the hotfix touches.

2. Stakeholders to convene (decision call, <30 mins)

• Incident commander (IC)
• Product lead requesting fix
• Data engineering lead (me)
• Data platform/SRE on-call
• Analytics/BI lead (consumer impact)
• Legal/compliance if PII/regulatory risk
• PM/Communications for downstream messaging

3. Quantify risks and benefits

• Restore time estimate vs. alternate mitigations (e.g., cache toggle, partial rollouts).
• Data-impact estimate: rows/tables/partitions at risk, reversibility (snapshots/backups), replication lag.
• Business impact: $$ per hour of outage, number of stakeholders, regulatory penalties.

4. Safety gates & constraints

• Require a backup snapshot + immutable export of affected partitions before applying.
• Test hotfix in a canary environment using a recent snapshot within 15–30 mins.
• Apply to <5% of traffic or a single replica first; monitor key metrics for 5–10 minutes.
• If hotfix mutates historical data, require automated data integrity checks post-change (row counts, checksums, reconciliations).

5. Rollback contingency

• Predefine rollback plan and time budget (e.g., 30 minutes). Prepare scripts to restore from snapshot and to rehydrate caches/dashboards.
• If irreversible corruption risk > threshold (e.g., >1% of historical dataset or PII exposure), disallow hotfix; prefer slower but safe remediation.

6. Decision criteria (binary)

• Approve only if: backup/snapshot created AND canary passed AND rollback tested AND business benefit > quantified risk threshold.
• Otherwise, propose mitigations (partial dashboards, guidance to users, extended SLAs).

7. Documentation & communication

• Log decision in incident doc: stakeholders, risk numbers, acceptance criteria, chosen path, timestamps, who approved.
• Notify stakeholders and downstream teams with expected impact window, monitoring links, and rollback criteria.
• After incident: run a postmortem with data validation artifacts, root cause, lessons, and plan to prevent future P0s (e.g., better feature flags, sandboxed hotfix process, automated backups).

This framework balances speed with data integrity by forcing measurable risk quantification, enforceable safety gates, and an explicit rollback/approval path so we restore service quickly without accepting uncontrolled, irreversible data loss.

Direct answer

An idempotent daily incremental-load DAG needs four things working together: staging-first writes that never touch the production table directly, an explicit run identifier scoping every write to exactly one logical attempt, deduplication before the final promotion, and a metadata record of what was actually committed. Together these mean retrying the same logical day, whether from a single task retry or a full DAG rerun, converges to the same final state instead of duplicating or losing rows.

Structured elaboration

Task granularity. Split the load into distinct tasks along natural idempotency boundaries: extract-to-staging, pre-checks, deduplicate-within-staging, atomic-swap-or-merge-into-target, and record-metadata. Splitting this finely, rather than one monolithic load task, means a failure at, say, the pre-check step can retry just that step without re-extracting, and it gives a clear per-step audit trail of exactly what happened on a given run.

Staging tables. The extract task writes to a staging table scoped by run identifier, either a uniquely named table per run or a shared staging table with a run_id column, never directly to the production table. A retried or even duplicated extract simply produces another (or overwrites the same) staging row set, with zero risk to the already-correct production table until the swap step deliberately promotes it.

Run identifiers. Every staged row carries an explicit run identifier, typically the DAG run's logical date combined with a monotonic attempt number, rather than relying on wall-clock insert time. This is what lets deduplication and metadata recording answer "which specific attempt produced this data" unambiguously, instead of inferring it from timing, which breaks under retries and backfills.

Pre-checks. Before promoting staged data, validate the row count against an expected historical range, validate that the expected columns are present, and validate that no key column has an unexpected null rate. A pre-check failure should halt before the swap, not attempt to promote suspect data and hope a downstream consumer catches it later.

Deduplication. Within staging, deduplicate on the row's natural business key, not a surrogate row identifier that would not survive a retried extract, keeping the most recent version when duplicates exist from overlapping incremental windows.

Atomic swap or MERGE. Promote staging into production either through an atomic rename or swap (the staging table becomes the new partition instantaneously) or a MERGE (or INSERT ... ON CONFLICT DO UPDATE) keyed on the business key, so a retry of this exact step, even after a partial completion, converges to the same end state rather than duplicating already-promoted rows.

Metadata recording. After a successful promotion, write a row to a metadata table (run_id, logical_date, row_count, status='committed', committed_at) that acts as the definitive record of what has actually been committed for that logical date. A subsequent run for the same date checks this metadata first, so an accidental double-trigger, two DAG runs for the same date somehow both firing, can detect the prior commit and skip redundant work rather than blindly reprocessing.

Transactional writes to the warehouse. Where the target warehouse supports it, wrap the final swap or merge and the metadata-recording insert in a single database transaction, so a crash between "data promoted" and "metadata recorded" cannot happen: either both commit, or neither does. This closes the small window that staging, run identifiers, and deduplication alone leave open, where data has landed in production but the metadata table does not yet know it, which could otherwise let a naive check-metadata-first design re-promote data that already landed.

Handling partial DAG failure explicitly. If the DAG fails partway, for example after the swap but before metadata recording, in a warehouse where the two cannot share one transaction, the retry logic must re-check the actual target-table state rather than re-run blindly. The metadata-recording task itself, on retry, should verify whether its own prior attempt already landed, an idempotent metadata write keyed on run_id such as INSERT ... ON CONFLICT DO NOTHING, rather than assuming a clean slate every time it runs.

Worked example

A daily incremental load of an orders table runs with run_id = 20260720_020000_attempt1. The extract lands 48,213 rows into staging_orders. The pre-check's expected row-count range is 40,000 to 60,000 based on historical daily volume, so 48,213 passes.

Deduplication finds 312 duplicate business keys from an overlapping incremental window with yesterday's late-arriving data, and keeps the most recent occurrence of each, leaving:

$48213 - 312 = 47901 \text{ unique rows}$

The MERGE into the orders production table affects all 47,901 rows, each either updating an existing row or inserting a new one depending on whether its business key already exists. The metadata table then records run_id=20260720_020000_attempt1, row_count=47901, status=committed.

Later that same day, the DAG is manually retried after a downstream consumer flags something and asks for a rerun. The metadata-check-first step queries the metadata table, finds logical_date=2026-07-20 already has a committed row with row_count=47901, and either skips straight to a no-op success if the retry was accidental, or, if the source data genuinely changed since the last run, proceeds through the same extract, deduplicate, merge, and metadata sequence again under a new run_id, landing whatever the currently correct row count is through the same idempotent MERGE, converging to the correct final state regardless of how many times it is repeated.

Trade-offs and pitfalls

Over-fragmenting task granularity, a separate task for every tiny sub-step, adds scheduling overhead and dependency-graph visual complexity that may not be worth it for a small pipeline; granularity should track genuine retry and audit boundaries, not be maximized for its own sake.

The metadata-check-first pattern only actually prevents double-processing if every code path that can trigger a run, including a human manually running the DAG from the orchestrator's own user interface, goes through it. A common gap is a manual "clear and rerun" action that bypasses the intended metadata check entirely.

Deduplication that blindly keeps the latest occurrence can silently mask a genuine data-quality bug: if a business key should never legitimately appear twice in a correct extract, always deduplicating instead of alerting on the first unexpected duplicate trades away a real correctness signal for a smoother-looking run.

Problem: top-10 products by avg rating last 30 days under heavy writes. Goals: fast aggregate reads without slowing writes.
Schema changes and indexes:

• Add a write-optimized summary table reviews_agg(product_id, window_day date, review_count int, rating_sum int, rating_avg float) updated incrementally.
• Maintain recent-window rolling buckets, e.g., daily or hourly buckets, then compute 30-day averages by aggregating these buckets.
• For low-latency leaderboard, maintain a materialized view or in-memory cache (Redis/KeyDB) keyed by day and product with sorted sets for top-N.
  Implementation:
• On insert of review, write to reviews table (append-only) and asynchronously push a lightweight event to a background worker/queue (Kafka/RabbitMQ).
• Worker updates reviews_agg: increment count and sum for current day using atomic DB statements (UPSERT) or update via idempotent increments.
  Indexes:
• Primary key on reviews_agg(product_id, window_day) for fast upsert.
• Index on reviews_agg(window_day, rating_avg DESC) to compute top-N per day; or maintain precomputed leaderboard in cache (sorted set by avg).
  Why this helps:
• Heavy writes: main write stays append-only with minimal synchronous work; aggregation is handled asynchronously, avoiding write contention and expensive full-table scans.
• Aggregation: computing top-10 for last 30 days becomes a small aggregation over 30 rows per product (or precomputed daily scores), or merging top lists from cache shards.
  Edge cases:
• Ensure idempotency and eventual consistency; provide fallback exact query (slower) if aggregator lag is unacceptable. Use background workers with retries and metrics.

Parentheses change what OR groups with, and SQL's default precedence (AND binds tighter than OR) can silently produce the wrong result if you assume left-to-right reading.

Structured elaboration

Without parentheses, WHERE category = 'shirts' AND price < 20 OR on_sale = true is evaluated as (category = 'shirts' AND price < 20) OR (on_sale = true): any row that is on sale qualifies, category and price notwithstanding. Adding parentheses around the OR, category = 'shirts' AND (price < 20 OR on_sale = true), restricts the OR branch to shirts only. Neither form is "wrong" in isolation; the bug is writing one when you meant the other. NOT is the third operator the question names, and its precedence is the highest of the three: SQL evaluates NOT before AND, and AND before OR. So NOT category = 'shirts' AND price < 20 parses as (NOT category = 'shirts') AND (price < 20), not as NOT (category = 'shirts' AND price < 20); the NOT applies only to the single comparison immediately next to it, not to the whole AND expression, unless parentheses say otherwise.

Worked example

Using products(product_id, category, price, on_sale) with rows (1, shirts, 15, not on sale), (2, shirts, 25, on sale), (3, pants, 10, on sale), (4, shirts, 30, not on sale):

• Without parentheses: rows 1, 2, 3 match. Row 3 (pants) sneaks in purely because it's on sale.
• With parentheses: rows 1, 2 match. Row 3 is correctly excluded because it isn't a shirt.
• Adding NOT: WHERE NOT category = 'shirts' AND price < 20 matches only row 3 (pants, price 10). The NOT applies to category = 'shirts' alone (true for rows 1, 2, 4, so NOT makes it false for them), then ANDs that with price < 20, leaving only the one row that is both not-a-shirt and under $20. Wrapping the whole expression instead, WHERE NOT (category = 'shirts' AND price < 20), flips which rows are excluded: it matches rows 2, 3, and 4, everything except row 1 (the only row where category = 'shirts' AND price < 20 was true to begin with).

Trade-offs and pitfalls

The fix costs nothing and the failure mode is silent (no error, just wrong rows), which is what makes it dangerous in report queries nobody double-checks. As a habit: whenever AND and OR appear in the same WHERE clause, add explicit parentheses even where they aren't strictly required, purely for the next reader. The same silent-wrong-result risk applies to NOT: many people mentally read NOT a AND b as NOT (a AND b), but SQL does not; when NOT needs to negate a whole compound condition rather than just the term next to it, parenthesize it explicitly.

Direct answer

Sort events by user and time, compute the inter-event gap per user with a grouped diff(), mark a new session wherever that gap exceeds the inactivity threshold, and turn those markers into session ids with a grouped cumulative sum. Every step is a vectorized pandas op (sort_values, groupby().diff(), cumsum(), groupby().agg()), so the whole function has no Python-level row loop.

Approach

import pandas as pd

def sessionize(events: pd.DataFrame, inactivity_threshold_minutes: int = 30) -> pd.DataFrame:
    df = events.copy()
    df['event_time'] = pd.to_datetime(df['event_time'])
    df = df.sort_values(['user_id', 'event_time']).reset_index(drop=True)

    # gap since the previous event for the same user, in minutes
    df['dt_minutes'] = (
        df.groupby('user_id')['event_time']
          .diff().dt.total_seconds().div(60).fillna(0)
    )

    # start a new session whenever the gap exceeds the threshold
    df['new_session'] = (df['dt_minutes'] > inactivity_threshold_minutes).astype(int)
    df['session_idx'] = df.groupby('user_id')['new_session'].cumsum()
    df['session_id'] = df['user_id'].astype(str) + '_' + df['session_idx'].astype(str)

    # only count forward progress in playback as "play time" (ignore seeks backward)
    df['playback_delta'] = (
        df.groupby(['user_id', 'session_idx'])['playback_position_seconds']
          .diff().clip(lower=0).fillna(0)
    )

    sessions = df.groupby(['user_id', 'session_id', 'session_idx']).agg(
        start=('event_time', 'first'),
        end=('event_time', 'last'),
        duration_seconds=('event_time', lambda x: (x.max() - x.min()).total_seconds()),
        total_play_time=('playback_delta', 'sum')
    ).reset_index(drop=False)

    return sessions[['user_id', 'session_id', 'start', 'end', 'duration_seconds', 'total_play_time']]

Worked example

events = pd.DataFrame({
    'user_id': [1, 1, 1, 1, 2, 2],
    'event_time': pd.to_datetime([
        '2026-01-01 10:00', '2026-01-01 10:10', '2026-01-01 11:00', '2026-01-01 11:05',
        '2026-01-01 09:00', '2026-01-01 09:20'
    ]),
    'event_type': ['play'] * 6,
    'playback_position_seconds': [0, 600, 0, 300, 0, 1200],
})
print(sessionize(events, inactivity_threshold_minutes=30))

Output (verified by running this exact code): user 1's first two events are 10 minutes apart (under the 30-minute threshold, same session), then a 50-minute gap before the next event starts a second session; user 2's two events are 20 minutes apart, one session. Three session rows come back, with session_id values 1_0, 1_1, 2_0, duration_seconds of 600.0, 300.0, 1200.0 respectively, and total_play_time matching those durations since playback only moves forward in this fixture.

Key points

• Fully vectorized: sort_values, grouped diff, cumsum, grouped agg, no Python-level loop over rows.
• total_play_time only counts positive increases in playback_position_seconds (via .clip(lower=0)), so a rewind or a seek-back within a session does not get counted as negative play time or cancel out real progress.
• session_id is built as a deterministic string (user_id plus a per-user session counter); swap in a UUID if you specifically need globally unique, non-guessable ids.

Complexity

Dominated by the sort: O(n log n) in the number of events. The grouped diff, cumsum, and final agg are each O(n). Memory is O(n) for the intermediate columns added to df, plus O(s) for the output, where s is the number of sessions.

Edge cases

• A user with a single event produces a single-event session with duration_seconds == 0.
• Missing playback_position_seconds: .diff() on a NaN (not-a-number) value propagates NaN, and .fillna(0) after the clip turns that into "no measured progress" for that step rather than crashing the aggregation; decide if that is the right default for your data or if it should be flagged separately.
• Out-of-order or duplicate timestamps for the same user: the initial sort_values fixes ordering, but two events with the identical timestamp for one user will not error, they just process in whatever order the sort's tie-break leaves them in, which may not be the true event order if the source system does not guarantee microsecond-level uniqueness.

Testing correctness and performance

• Unit tests on small synthetic frames covering gaps just below and just above the threshold, playback resets and seeks, and duplicate timestamps.
• Property tests: total event count per user equals the sum of per-session event counts; every session boundary corresponds to a gap strictly greater than the threshold.
• Cross-check against a simple (slow, loop-based) reference implementation on the same random small dataset to confirm the vectorized version agrees exactly.
• For performance, benchmark on realistic-sized data with timeit and profile memory with tracemalloc; for data too large for one process's memory, evaluate chunked processing keyed by user_id, or a distributed engine, since a session boundary never crosses users so the work parallelizes cleanly by user.