DoorDash Senior Data Engineer Interview Preparation Guide

Direct answer

Log aggregation collects unstructured or semi-structured text output that applications and infrastructure already emit (request logs, error traces, system events) using a lightweight agent running alongside the source, and ships it to a central broker for downstream processing. It differs from a structured event stream in one fundamental way: a log line is written for a human to read, with no guaranteed schema, while a structured event is deliberately designed as data, with a defined shape a consumer can rely on; that difference shapes everything about how you have to handle log ingestion.

Structured elaboration

What is typically collected

• Application logs (request/response traces, error stack traces, application-level warnings), system logs (OS and infrastructure-level events), and access logs (web server or load balancer request records).

Common collection agents

• Filebeat: a lightweight, low-resource agent focused specifically on tailing log files and forwarding them, commonly paired with the Elastic stack.
• Fluentd (and its lighter sibling Fluent Bit): a more general-purpose log collector and router with a large plugin ecosystem for routing to many different destinations.
• Logstash: a heavier-weight collector with built-in parsing and transformation capability, often used when logs need real processing before landing, not just forwarding.

How a log stream differs from a structured event stream

• Schema: a structured event has a defined, versioned shape a consumer can validate against; a log line is free text whose format can change any time a developer changes a log statement, with no contract and often no warning.
• Intent: a log line is written primarily for a human debugging an issue in the moment; a structured event is written primarily for a downstream SYSTEM to consume programmatically. Extracting reliable structured data out of logs (parsing timestamps, fields, and values out of free text) is a real engineering task in a way that reading an already-structured event is not.
• Volume characteristics: logs are often far higher-volume and noisier than a purpose-built event stream, since every application already emits a lot of log output that was never designed with ingestion cost in mind.

Handling log rotation

• A log agent needs to track its read position (an inode and offset, not just a filename) so it correctly continues reading a rotated file's new incarnation without either re-reading already-shipped lines or silently skipping lines written in the brief window around rotation.

Handling multi-line log entries

• A single logical log entry (a stack trace spanning many lines, for example) needs to be recognized and grouped correctly by the agent before shipping, using a pattern (a line starting with a timestamp signals a new entry, everything after it until the next timestamp belongs to the same entry) rather than treating every physical line as a separate record, which would shred a stack trace into meaningless individual fragments.

Handling backpressure

• When the central broker cannot keep up with the volume an agent is producing, the agent needs a bounded local buffer (disk-backed, ideally, so a restart does not lose buffered lines) and a policy for what happens when that buffer fills: block and slow the source application (risky, since logging should rarely be allowed to affect application performance), drop the oldest or newest lines (a real, explicit data-loss decision that should be a deliberate choice, not a default nobody decided on), or sample.

Worked example

An application server emits a Java stack trace as 15 separate physical lines when an exception occurs. A naive line-by-line log shipper would forward each of those 15 lines as 15 unrelated log entries, destroying the ability to read the actual stack trace as a coherent unit downstream. Configuring the collection agent (Filebeat or Fluentd, either supports this) with a multi-line pattern, "a new entry starts only on a line matching a timestamp pattern, otherwise append to the previous entry," correctly reassembles the full 15-line trace into one shipped log entry, matching how a human reading the raw file would naturally interpret it.

Trade-offs & pitfalls

• Treating log data as if it had the same reliability as a structured event stream is a common mistake; a developer can change a log line's format in a routine code change with zero awareness that a downstream parser depends on its exact shape, and that parser will then silently misparse or drop fields with no warning from anywhere in the chain.
• Getting multi-line grouping wrong in either direction is costly: too aggressive grouping merges genuinely separate log entries into one garbled record, too conservative grouping shreds a single logical entry (like a stack trace) into fragments; test the pattern against real production log samples, not synthetic ones.
• A backpressure policy of "just buffer more" without a bound eventually exhausts local disk on the source host, which then risks affecting the actual application, the opposite of what a good logging design should ever do.
• Retention and cost for raw log volume can dwarf structured event costs at real scale, since logs are typically far higher-volume and lower information-density per byte; a deliberate retention and sampling policy matters more here than for most other source types.

Direct answer. Frame it in terms they already care about (report speed, or the cost of the current slow experience), name the concrete cost honestly (the write-latency or storage number, not a vague "some overhead"), and commit to a specific way you'll confirm afterward that it actually delivered the promised benefit.

Structured elaboration. Lead with the business-visible symptom and its fix, not the technical mechanism: "this report currently takes X seconds and blocks people from Y; this change gets it to Z seconds" lands better than opening with index internals. State the cost in the same concrete terms: a specific percentage increase in write latency, or a specific amount of additional storage, rather than a hedge like "a little overhead," since a specific number is both more honest and more persuasive precisely because it shows you've actually measured it rather than guessing. Explain briefly WHY the cost exists (every write now has to update this extra structure too) so the trade-off feels like a reasoned decision rather than a black box. Close with how you'll verify the benefit actually materialized after shipping, a specific metric you'll check and a specific timeframe, so the stakeholder isn't just taking your word for it.

Worked example. "Right now this report takes the better part of a minute to load, and three people have told me they've stopped using it because of that. Adding this index gets it to load near-instantly, based on my testing. The cost is a roughly 10% increase in write latency for this specific table, workload testing suggests that stays well within our current write-latency budget, and I'll check actual production write latency and report load time again a week after we ship, to confirm both numbers hold up outside of my test environment, not just in it."

Trade-offs and pitfalls. Resist the urge to round the cost down or bury it in technical language to make the ask easier to approve; a stakeholder who later discovers an unmentioned cost, even a small one, loses trust in every future recommendation you bring them, which is a much larger cost than the discomfort of stating the trade-off plainly up front.

Direct answer. Co-partitioning means a fact table and a dimension table are both physically partitioned by the same key using the same scheme, so every worker holding a slice of the fact table also holds the exact matching slice of the dimension table, letting the join happen entirely locally on each worker with zero data movement between them.

Structured elaboration. In a distributed system, a join between two tables ordinarily requires either broadcasting a small table to every worker or shuffling both tables so matching keys land together; co-partitioning sidesteps both by arranging, ahead of time, for the partitioning scheme itself to guarantee that co-located data. If orders and customers are both partitioned by, say, a hash of customer_id using the identical hash function and number of partitions, then partition N of orders and partition N of customers are guaranteed to hold all the rows that could ever match each other, and nothing outside partition N could possibly match anything inside it, so each worker can join its own local slice of both tables with no network traffic for the join itself.

Worked example. A fact table of orders and a dimension table of customers, both partitioned into the same number of buckets by the same hash of customer_id, let every worker compute its slice of the join purely locally; the same two tables, if the dimension table were instead unpartitioned or partitioned differently (by, say, region instead), would lose that guarantee entirely and fall back to a broadcast or shuffle strategy for the join.

Trade-offs and pitfalls. The benefit is conditional on the partitioning staying genuinely aligned: if one table's data grows or its key distribution shifts and only one of the two tables gets repartitioned to compensate (a common operational drift), the co-location guarantee silently breaks and joins fall back to a full shuffle without any explicit warning, since the query itself still runs and returns correct results, just far more expensively than expected. A skewed key within an otherwise well-co-partitioned scheme also erodes the benefit locally: a hot key still concentrates work on whichever single worker holds that partition, even though no shuffle was needed to get the matching data there.

Direct answer

I'd start by pulling the itemized billing export (AWS's Cost and Usage Report, or the equivalent from Azure or Google Cloud) rather than the headline invoice total, break the delta down by service, region, and tag, and look for whether the jump is broad (many services up a little) or concentrated (one or two line items driving most of it), because that single split determines whether I'm hunting for a config change or a runaway process. In parallel, I'd put an emergency budget alert and a hard cap or throttle in front of the likeliest culprit so I'm not still bleeding money while I investigate, since stopping the bleeding and finding the root cause are two different, simultaneously-runnable tasks, not a sequence.

Structured elaboration

Step 1: Scope the delta before hypothesizing about the cause
Pull the granular billing export for both months and diff them by service, region, account or project, and tag. A 40% jump could be one resource type up 400% or every service up a proportional amount, and those point to completely different investigations, so this diff is the first thing to run, before forming any hypothesis about root cause.

Step 2: Narrow to the resources actually driving the delta
Match the billing line items with the largest deltas back to specific resource IDs and tags. Look specifically for: new or unusually large resource counts, a spike in a service that's normally near-zero for this account, unattached storage volumes that shouldn't exist, or a jump in data-transfer line items, since egress spikes are a common and easy-to-miss cause of a sudden bill jump.

Step 3: Correlate against what changed operationally, in the same window
Cross-reference the spike window against deployment history and CI/CD logs (did a release ship right before the jump started), autoscaling events (did a scaling policy misfire and hold capacity high), and application/infrastructure logs for anything that looks like a runaway job or a misconfigured batch process. This is where "a new deploy shipped a debug flag that left instances scaled up" or "a scheduled job got triggered twice" typically surfaces.

Step 4: Form and test a specific hypothesis
By this point there's usually a short list of candidates (a specific deploy, a specific job, a specific resource type). Validate each by reproducing the cost against the actual usage: does the resource's usage-hours or data-scanned metric, for the spike window, actually account for the dollar delta observed. A hypothesis that doesn't reproduce the dollar amount isn't the (whole) root cause, and the investigation isn't done until the numbers actually add up.

Step 5: Stop further unexpected spend while still investigating
This runs in parallel with steps 1 through 4, not after them: set an emergency budget alert on the affected account or service immediately, and for the most likely culprit, apply a targeted control (scale the autoscaling group's max down to a safe ceiling, pause the suspect job, revoke a deployment credential if a bad deploy is implicated) rather than a blanket freeze that would stop legitimate traffic too. The goal is to bound the damage on the specific thing under suspicion without causing a second incident by shutting down something that turns out to be unrelated.

Worked example

A team's monthly bill jumps from a baseline of $42,000 to $58,800, a 40% increase, or $16,800 in absolute terms. The billing-export diff shows the increase isn't broad: EC2 (Amazon Elastic Compute Cloud) compute is up only 4%, but a single service, a managed data-warehouse query engine, is up $11,200, and S3 (Amazon Simple Storage Service) data-transfer/egress charges are up $4,100, together accounting for $15,300 of the $16,800 delta, with the remaining $1,500 spread thinly across normal month-over-month growth.

Matching the query-engine spike to usage shows a single ad-hoc analytics query scanned roughly 2 TB of data three separate times during the window, run by an analyst testing a new dashboard against a table that wasn't partitioned the way they assumed, at a rate where scanning that much unpartitioned data repeatedly accounts for the bulk of the $11,200. The egress increase correlates with a deploy that changed a batch export job's destination from an in-region bucket to a bucket in a different region, adding a per-GB cross-region transfer charge that hadn't existed before that release.

Root cause: two independent, unrelated changes landing in the same billing period, an expensive unpartitioned ad-hoc query pattern and a misconfigured cross-region export destination, together explaining $15,300 of the $16,800 (91%) increase, with the remainder attributable to normal growth.

Trade-offs and pitfalls

The most common mistake is jumping straight to a hypothesis (usually "it's autoscaling" or "it's a new feature") before actually running the billing diff, which wastes time chasing a plausible-sounding cause that the data doesn't support. A second is applying a blanket spend freeze while investigating, which stops the bleeding but can also take down legitimate production traffic, turning a cost incident into an availability incident. A third is declaring victory once you've found a cause that "sounds right" without checking that the dollar amount it explains actually adds up to the observed delta. As this example shows, a real spike is often more than one thing happening at once, and stopping the investigation after finding the first plausible cause can leave a second, still-active cost driver running unnoticed.

Direct answer

Model retention and revenue at the PLAN level, not the customer level: a customer who downgrades or pauses is still "retained" as a customer even though their plan-level revenue changed, so the cohort curve needs separate logic for whether the CUSTOMER stayed versus whether the ORIGINAL PLAN'S revenue was retained, expanded, or contracted, with proration and refunds explicitly reconciled rather than assumed away.

Structured elaboration

• Why customer-level and plan-level definitions diverge: a customer who downgrades from a $100/mo plan to a $60/mo plan has NOT churned as a customer (they're still paying, still active), but has produced $40/mo of REVENUE churn on their original cohort's plan-level curve. Conflating the two produces a retention curve that's technically not wrong for either definition alone, but answers neither question cleanly if the two are mixed.
• Renewals, expansions, downgrades, and churn each need their own bucket: renewal (same price continues), expansion (price increases, e.g. an upsell or add-on), downgrade (price decreases but customer remains), and full churn (customer stops paying entirely). A revenue-retention curve typically reports NET revenue retention, which nets expansions against downgrades and churn within the same cohort.
• Proration: a plan change partway through a billing period produces a partial-period charge at each of the old and new price; decide whether the cohort curve attributes that month's revenue at the blended prorated amount or simplifies to whichever plan was active longer that month, and state the choice.
• Multiple overlapping subscriptions per customer (a customer with two products, or a trial running alongside an existing paid plan) require deciding whether the cohort curve is computed per subscription or aggregated per customer; these produce different, both legitimate, answers.
• Validating the accuracy of the resulting curves: reconcile the SUM of a cohort's monthly plan-level revenue at each offset against an independent GAAP (Generally Accepted Accounting Principles)-style recognized-revenue figure -- i.e., the revenue number finance already reports for accounting purposes -- for the same customers and period, since a validated cohort curve should tie out to the finance team's own numbers, not just internally to itself.

Worked example

A cohort of 100 customers who signed up in the same month, each starting on a $50/mo plan. Suppose by month 3: 70 are still on the original $50/mo plan (pure renewal), 10 upgraded to $80/mo (expansion, +$30/mo each), 10 downgraded to $30/mo (downgrade, -$20/mo each), and 10 churned entirely (-$50/mo each). The cohort's month-3 plan-level revenue is $70(50) + 10(80) + 10(30) + 10(0) = 3500+800+300+0 = 4600$, against an original month-0 revenue of $100 \times 50 = 5000$. Net revenue retention at month 3 is $4600/5000 = 92\%$: expansions ($300 extra from upgrades) partially offset downgrades and churn ($700 lost combined), landing at 92% even though 10 of the original 100 customers stopped paying entirely. A customer-level retention curve for the same cohort at month 3 would instead report 90 customers still active (excluding only the 10 who fully churned), a DIFFERENT number answering a different question; presenting only one of these two curves without naming which one it is invites a stakeholder to draw the wrong conclusion from it.

A representative query producing the plan-level cohort curve directly from the schema in the question:

WITH cohort AS (
  SELECT customer_id, DATE_TRUNC('month', start_date) AS cohort_month
  FROM subscriptions
),
monthly_revenue AS (
  SELECT customer_id, DATE_TRUNC('month', event_date) AS revenue_month, SUM(amount) AS revenue
  FROM events
  WHERE event_type = 'payment'
  GROUP BY customer_id, DATE_TRUNC('month', event_date)
)
SELECT c.cohort_month,
  DATEDIFF('month', c.cohort_month, mr.revenue_month) AS month_offset,
  SUM(mr.revenue) AS cohort_revenue,
  COUNT(DISTINCT mr.customer_id) AS paying_customers
FROM cohort c
JOIN monthly_revenue mr ON mr.customer_id = c.customer_id
GROUP BY c.cohort_month, month_offset
ORDER BY c.cohort_month, month_offset;

Run against the 100-customer cohort above, this returns month_offset = 0 with cohort_revenue = 5000 and paying_customers = 100, and month_offset = 3 with cohort_revenue = 4600 and paying_customers = 90, reproducing both the 92% net revenue retention and the 90% customer-level retention computed above directly from the query rather than by hand.

Trade-offs & pitfalls

• A single "retention rate" without specifying customer-level vs. plan-level revenue is ambiguous and easy to misinterpret: always label which definition a chart is using, since the two can diverge substantially (as in the worked example, 90% customer retention vs. 92% net revenue retention are both true simultaneously and mean different things).
• Proration decisions materially change the numbers in a plan-change-heavy business and should be documented as an explicit modeling choice, not left as an implicit side effect of however the query happened to be written.
• Multiple subscriptions per customer is a common real-world wrinkle that a simplified one-subscription-per-customer model will get wrong; decide and document the unit of analysis (subscription vs. customer) up front.

Direct answer

Treat the request in two layers. First, delete the person's data everywhere it is stored as data: source tables, feature stores, training snapshots, caches and exports. Second, decide model by model whether the trained artifact still holds the person's information. Retraining is necessary only when it does, or when you cannot show that it does not. I would tell the user exactly what was deleted and what happens to models by a stated date, and the regulator only what a complaint or inquiry asks for, with counsel (the company's lawyers) deciding the legal position.

Step 1: Map what the person touched

A deletion ledger is a list of person IDs that every training job reads and excludes from its input. It stops the data coming back at the next run. Store the IDs hashed (run through a one-way scramble, with a secret key added so common IDs cannot be guessed) so the ledger itself does not become a list of people to leak; hashed IDs are still pseudonymous, which is acceptable for a list that exists only to enforce exclusion.

Pseudonymizing (swapping names for IDs while a key exists elsewhere) is not erasure: the data is still personal data. Only truly irreversible anonymization removes it, and that is hard to prove.

Step 2: When is retraining actually necessary?

Ask whether the model could reveal or reproduce this person's data.

• Low risk, no immediate retrain: a model trained on millions of rows with simple features (for example a gradient-boosted churn model, which combines many small decision trees and learns general patterns rather than storing rows) where one person has a negligible influence and the model outputs only a score. Exclude them via the ledger at the next scheduled retrain and record the date.
• High risk, retrain or retire promptly: a model that stores records (a nearest-neighbour lookup keeps the training records and returns the closest ones; an embedding index holds numeric fingerprints of text or items that can be mapped back to them), a fine-tuned language model that can regurgitate text, or a small model where the person is a large share of the data. Test with a membership-inference check (can an attacker tell whether a record was in the training set? Example: show the model 1,000 records it trained on and 1,000 it never saw, and let the attacker guess which is which. Guessing right 1,030 times out of 2,000 is 51.5%, close to the 50% of a coin flip, so no meaningful leakage; 90% would mean the model gives records away) and a probe for memorized strings. If the test cannot clear it, retrain without the person or roll back to a model trained before their data.
• Cost trade-off: a retrain costing days of compute can be batched into the normal cadence if risk is low. Machine-unlearning research methods (techniques that try to remove one person's influence from a trained model without retraining it) exist but are not settled enough to be your only defence.

Step 3: What to tell whom

• User: within one month of the request (Art. 12(3) sets one month, extendable by two further months for complex or numerous requests, with the person told of the extension and the reason within the first month): confirm deletion from systems, say that models will exclude them from the next training run by a date, and name any retained data with the legal reason (Art. 17(3) lists the exceptions to erasure, such as a legal obligation to keep invoices or the need to defend legal claims).
• Regulator: erasure requests are not themselves reported. If the user complains, show the record: ledger entry, model risk assessment, dates. Do not claim "the model forgot you" unless you tested it. Wording and any contested position go through counsel and the data protection officer (DPO, the person some organisations must appoint to advise on and monitor GDPR compliance).
• Recipients: tell processors (vendors that handle data on your behalf) and partners who received the data unless impossible or disproportionate effort (Art. 19).

Worked example

A support-ticket classifier is trained monthly on 2 million rows. A user asks for erasure on day 3. You delete their 14 tickets and features by day 5, add them to the ledger, and the day-30 training run excludes them. A membership check on the live model shows no leakage above chance, so you keep it until then and record that. If the same data had fine-tuned a model that quotes tickets, you retire it now.

I would frame the discussion around user impact, risk, and reversibility. A workaround is a temporary fix that reduces pain now, while the root issue is the underlying cause we still need to solve. I would ask: how many users are affected, how severe is the problem, and how risky is the workaround itself?

If the workaround is low risk and reversible, I would lean toward shipping it now and scheduling the root fix immediately after. For example, if users are blocked by a broken validation rule and we can safely relax it, I would ship the workaround, monitor errors, and commit to the deeper fix in the next cycle. If the workaround could corrupt data or create a bigger support burden, I would slow down and fix the root issue first.

I would make the decision explicit, document the trade-off, and assign an owner for the follow-up fix. That way the team is not pretending the workaround is the final answer, and users get relief as soon as it is safe to do so.

Direct answer

A declarative data-testing framework lets you state the expected properties of a dataset (a column is never null, a value falls in a range, a duplicate rate stays under a threshold) as data rather than as imperative test code, and have the framework validate any batch of data against that specification and produce a structured, human-readable report.

Structured elaboration

Using pandera (a lightweight, pandas-native validation library, representative of this class of tool alongside Great Expectations and dbt tests), a schema for the described transactions table:

import pandas as pd
import pandera.pandas as pa
from pandera.pandas import Column, DataFrameSchema, Check

schema = DataFrameSchema({
    "transaction_id": Column(str, unique=True),
    "user_id": Column(str, nullable=False),
    "amount": Column(float, Check.gt(0)),
    "occurred_at": Column(
        "datetime64[ns]",
        Check(lambda s: s >= pd.Timestamp.now() - pd.DateOffset(years=2)),
    ),
})

The fourth named assertion, that occurred_at falls within the last two years, is the Check(lambda s: ...) on that column: it compares every value in the column against "now minus two years" and fails any row whose transaction is stale, catching a case (an old, re-surfaced, or backdated record slipping into a supposedly-recent batch) that none of the other three checks would catch.

Running this against a two-row valid frame passes cleanly (verified by execution). Running it against a deliberately broken frame, a duplicated transaction_id, a NULL user_id, a negative amount, and an occurred_at more than two years in the past, with lazy=True to collect every failure rather than stopping at the first one, correctly surfaces all four problems in one structured report: a uniqueness failure on transaction_id, a not-nullable failure on user_id, a greater_than(0) failure on amount, and the lambda date-range failure on occurred_at (verified by execution: validating a frame with occurred_at=2022-01-01, more than two years before today, alongside the other three broken values, raises a SchemaErrors collection containing exactly these four failure rows, one per check, confirming the range check fires independently of the other three). The duplicate-rate-under-1% check is not a single-column constraint pandera expresses natively; it is implemented as a dataset-level custom check computing df['transaction_id'].duplicated().mean() < 0.01 and asserting it separately, or, in a more full-featured tool like Great Expectations, as a table-level "expect_column_values_to_be_unique" style expectation with a configurable mostly-threshold.

Trade-offs and pitfalls

What the declarative approach buys you over hand-written SQL/dbt tests: the schema doubles as living documentation of what "valid" means for this table (a new engineer can read the schema and understand the contract without reading test code), failures across many checks are collected and reported together rather than failing fast on the first one, and adding a new check is a one-line declaration rather than a new SQL query to write and maintain. Where it adds friction: there is a real learning curve to the framework's API and its own failure-mode nuances (lazy versus eager validation, how it handles nullable columns by default), and for a team already deeply invested in SQL/dbt tooling, introducing a second, Python-based validation layer adds a second CI dependency and a second place engineers need to look when a check fails, which is a genuine cost, not just a one-time learning tax.

Direct answer: Frame exclusion is a clause that removes rows from an already-computed frame, applied after the ROWS/RANGE/GROUPS bounds are evaluated, not instead of them. EXCLUDE CURRENT ROW removes only the current physical row from the frame, even if other rows tied with it (its "peers" under RANGE) remain. That is meaningfully different from ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, which is a purely physical frame that stops one row before the current one and never includes any peer, tied or not, that comes after it in sort order.

Structured elaboration

• Frame exclusion options (SQL:2011): EXCLUDE CURRENT ROW (drop only the current row), EXCLUDE GROUP (drop the current row and all its peers), EXCLUDE TIES (drop all peers except the current row itself), EXCLUDE NO OTHERS (the default: exclude nothing).
• Why it matters with ties: under a RANGE frame, rows with an identical ORDER BY value are peers and RANGE always includes or excludes an entire peer group together. EXCLUDE CURRENT ROW removes only the one physical row, so its peers still count. ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING, by contrast, is entirely positional: it simply stops one physical row short of the current one and has no concept of "peer" at all.
• Support varies by engine: EXCLUDE is part of the SQL:2011 standard and available in Postgres and several other engines, but not universally, so check target-engine support before relying on it.

Worked example

Table scores(id, score): (1,10), (2,20), (3,20), (4,30). Ids 2 and 3 tie on score = 20.

CREATE TEMP TABLE scores(id int, score int);
INSERT INTO scores VALUES (1,10), (2,20), (3,20), (4,30);

SELECT id, score,
  SUM(score) OVER (
    ORDER BY score
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE CURRENT ROW
  ) AS sum_excl_current,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS sum_rows_1_preceding
FROM scores
ORDER BY id;

Verified in DuckDB. Results:

For id=2: sum_excl_current = 30 (10 from id=1, plus 20 from its peer id=3; only id=2 itself is excluded). sum_rows_1_preceding = 10 (only the single physical row before id=2, which is id=1). For id=3: sum_excl_current = 30 (same peer group logic: id=3 is excluded, its peer id=2's 20 still counts, plus id=1's 10). sum_rows_1_preceding = 30 (physical rows before id=3 are id=1 and id=2: 10+20). The two ids that tie on score end up with different results between the two approaches specifically because RANGE + EXCLUDE keeps peer symmetry (both tied rows see the other's value) while the pure ROWS version does not (only the row physically earlier in sort order sees the other).

Key points

• EXCLUDE is a post-processing step on an already-defined frame; it does not change how RANGE or ROWS determines the frame's boundaries in the first place.
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING includes exactly the physical rows before the current one, full stop.
• RANGE ... CURRENT ROW EXCLUDE CURRENT ROW includes every row up to and including the current row's whole peer group, then drops just the current row, leaving other peers in.

Complexity
Both queries require one O(n log n) sort by score, then a single incremental pass. Frame exclusion doesn't add asymptotic cost: it's implemented as a bookkeeping adjustment (skip the current row, or the whole peer group, when reading from the already-maintained running frame) rather than a second pass over the data.

Edge cases

• The first row in sort order (id=1, score=10): no rows precede it under either approach, so both results are NULL, which is expected, not a bug.
• EXCLUDE GROUP instead of EXCLUDE CURRENT ROW: would drop the entire peer group for a tied row, not just the current physical row, giving id=2 and id=3 both sum = 10 (only id=1) instead of 30.
• All rows tied to a single value: every row would compute against the same peer group; EXCLUDE CURRENT ROW still only drops the one physical row each time, so different rows in that group can still get different results depending on what else is elsewhere in the frame.

Trade-offs & pitfalls

The common wrong turn is assuming EXCLUDE CURRENT ROW behaves like a ROWS-based "skip one row" adjustment; it doesn't, because it operates on the RANGE frame's peer semantics. If you actually want "everything strictly before this physical row" with no peer awareness, reach for ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING directly and skip EXCLUDE entirely. Reserve EXCLUDE for cases where you deliberately want peer-aware behavior, such as computing "the sum of everyone else in my tie group" for a leaderboard or fairness calculation, which ROWS ... 1 PRECEDING cannot express at all since it has no notion of ties.

Direct answer: Deduplicate transactions by transaction_id, exclude is_test = true rows, subtract refund_amount from amount per transaction, then aggregate by user and day.

Structured elaboration and executable SQL:

WITH deduped AS (
  SELECT DISTINCT transaction_id, user_id, amount, refund_amount, occurred_at, is_test
  FROM transactions
)
SELECT user_id, date(occurred_at) AS day,
       SUM(amount - refund_amount) AS net_spend
FROM deduped
WHERE is_test = false
GROUP BY user_id, date(occurred_at);

Deduplicating BEFORE filtering and aggregating (rather than trying to fold DISTINCT into the final SUM) is what protects against a genuinely duplicated raw row silently doubling both the amount and its corresponding refund_amount.

Worked example (executed): transactions: t1 (user1, amount=100, refund=0) appearing as two identical duplicate rows; t2 (user1, amount=50, refund=20, a partial refund); t3 (user2, amount=30, is_test=true). Run against SQLite, the query returns (user1, 2026-06-01, 130.0): the duplicate t1 row collapses to one via DISTINCT (contributing 100), t2 contributes its net 30 (50-20), summing to 130; user2's row is correctly excluded entirely since it's a test transaction, and no row appears for user2 at all, rather than a spurious zero.

Trade-offs & pitfalls: DISTINCT on the full row (not just transaction_id) is a common but fragile dedup shortcut: it only works if a duplicated row is BYTE-IDENTICAL across all selected columns; if a retried write updates even one column (e.g., a slightly later occurred_at timestamp on the retry), the naive DISTINCT would fail to collapse it, and a ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY ...) pattern (as used in the raw-event dedup earlier in this topic) is the more robust choice for production use. Also confirm whether refund_amount can EVER exceed amount (a data-quality invariant worth testing for, not assuming); if it can, net spend could go negative for a single transaction, which may or may not be valid depending on whether refunds can span or exceed the original charge (e.g., a goodwill credit on top of a full refund).