Build the safeguards
Eight explained steps, a source example and a checkpoint at each decision.
Read the tutorialPostgreSQL / Analytics / Data correctness
Compare the choices.
Choose a pattern by the failure it addresses. Several safeguards can be necessary at once.
Which approach fits the problem?
| Approach | Useful when | Decision or limitation |
|---|---|---|
| Preaggregate then join | Measures stored at a coarser grain | Preserves that grain when the join key is unique |
| EXISTS | Filter records by a related match | Avoids multiplying the selected record |
| Window functions | Rank, deduplicate or compare within groups | Require explicit partitioning and stable ordering |
| Event-time reporting | Describe when a business event happened | Needs a receipt cutoff for reproducible snapshots |
| UTC reporting day | A business definition that explicitly uses UTC | Does not represent every customer’s local day |
| Local midnight boundaries | Calendar reporting in a named zone | Must handle daylight saving and historical zone rules |
Which environment should you use?
| Environment | What you can do | Scope |
|---|---|---|
| This browser lab | Click, compare, reset and inspect exact source | Actual PostgreSQL runs through the open-source PGlite runtime in a fresh in-memory worker. Four invented orders explain correctness. Their query plans do not establish warehouse cost, index benefits or production performance. |
| Independent download | Run the same prebuilt lab with Python’s local server | No package installation or automatic external calls needed for the prebuilt preview |
| Native exercise | Run language tests and the included local adapter or script | Requires the documented local toolchain; does not provision a cloud service |
A comparison you can reproduce
Revenue is 42000 cents with the multiplying join and 30000 at order grain. The local-day example returns 26000 cents; the UTC-day query returns 24000. The safeguarded cohort has one A user and two B users.
Keep the source inputs and business definition fixed when comparing one implementation change. The cohort example deliberately makes late-arrival policy visible; hold its cutoff constant when isolating a query rewrite.
What this adds to a conventional tutorial
Begin with an observable outcome, inspect the decisions that produced it, then test a repair. The companion tutorial explains the mechanism; the how-to guide takes the same source into an independent environment.
Read the primary documentation
Rebuy’s staff role calls for accurate experimentation reports, data models and analytical queries.
Choose your next step
Compare the alternatives
Understand where each pattern helps and which tradeoffs remain.
Compare approachesRun and extend it locally
Download the independent environment, run the native checks and try a specific extension.
Open the how-to guideLab discussion / SQL Reporting Lab
What would you try next?
Ask about the code, compare an approach, or describe an edge case. Reader comments are reviewed before publication.
Code walkthrough
Follow the implementation in code.
These short excerpts connect the lab’s behavior to its implementation. Each example links to the full tutorial step for its context, tradeoffs and checkpoint. Names and dependencies belong to that source; the excerpts are not standalone programs.
Write the reporting contract
The fixture stores revenue in integer cents on orders. Lines represent items, not additional revenue. State the expected population, grain, currency, time zone and cutoff before writing the query. A report definition is a product decision that a query merely implements.
SELECT id, amount_cents FROM orders ORDER BY id;Restore the intended grain
Aggregate the many side to one row per order before joining. Another valid approach is an EXISTS filter when you only need to know whether a matching line exists. SUM(DISTINCT amount) is not a substitute: two legitimate orders can share an amount.
WITH item_counts AS (
SELECT order_id,COUNT(*) FROM lines GROUP BY order_id
)
SELECT SUM(o.amount_cents) FROM orders o
JOIN item_counts i ON i.order_id=o.id;Choose one exposure per person
Repeated exposures should not automatically create repeated people in an experiment denominator. ROW_NUMBER partitions events by person and uses event time plus event ID to choose a stable first exposure. The chosen variant becomes that person’s attribution rule in this exercise.
ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY happened_at,event_id
)Separate event time from receipt time
The fixture includes an exposure that arrived late. The comparison makes its receipt cutoffs explicit, so it demonstrates a policy difference as well as deduplication. For an isolated experiment, first hold the cutoff constant and then compare query structure. Event time alone cannot describe a reproducible data snapshot.
WHERE happened_at < event_cutoff
AND received_at < receipt_cutoffBuild calendar boundaries in the business zone
The New York day that ends daylight saving lasts 25 hours. Translate both local midnights to instants and use a half-open interval. Applying a function to every timestamp can complicate index use; calculating boundaries keeps the filtered column straightforward.
WHERE ordered_at >= (TIMESTAMP '2026-11-01' AT TIME ZONE 'America/New_York')
AND ordered_at < (TIMESTAMP '2026-11-02' AT TIME ZONE 'America/New_York')