SQL Reporting Lab
By Chason ArthurRevision 127.036Published Updated

PostgreSQL / Analytics / Data correctness

Build the safeguards.

Follow SQL Reporting Lab from its visible failure to a testable implementation. Each step names one decision and a checkpoint.

  1. Write the reporting contract
  2. Observe join multiplication
  3. Restore the intended grain
  4. Choose one exposure per person
  5. Aggregate outcomes before counting conversions
  6. Separate event time from receipt time
  7. Build calendar boundaries in the business zone
  8. Verify meaning before tuning the plan

Step 1 of 8

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;

Checkpoint: Sum the four order amounts by hand: 30000 cents.

Step 2 of 8

Observe join multiplication

An order with two matching lines appears twice in a direct join. COUNT(*) counts joined rows, and SUM(order.amount_cents) repeats the order’s revenue. Inspect the source records rather than assuming that a plausible total is correct.

SELECT o.id,o.amount_cents,l.sku
FROM orders o JOIN lines l ON l.order_id=o.id;

Checkpoint: Locate the two joined rows for order 1.

Step 3 of 8

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;

Checkpoint: Explain why the repaired join now has four order rows.

Step 4 of 8

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
)

Checkpoint: Find the two exposure events belonging to user 101.

Step 5 of 8

Aggregate outcomes before counting conversions

One person may place several orders. Count that person once as converted, while summing their eligible revenue separately. The lab uses a seven-day post-exposure window. Other products may need different rules; changing a window changes the business question.

COUNT(*) FILTER (WHERE converted) AS converted_users

Checkpoint: User 101 has two orders and one converted-person outcome.

Step 6 of 8

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_cutoff

Checkpoint: Explain which records would change if the receipt cutoff moved earlier.

Step 7 of 8

Build 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')

Checkpoint: Find the late UTC order that still belongs to November 1 locally.

Step 8 of 8

Verify meaning before tuning the plan

The playground displays PostgreSQL’s actual plan for the tiny fixture. Save expected result sets before changing indexes or rewriting joins. A production investigation then needs realistic distribution, cardinality estimates, buffers and measured execution. A warehouse’s billing model is a separate measurement.

EXPLAIN SELECT COUNT(*) FROM orders;

Checkpoint: Describe the additional data needed before making a performance claim.

Practice the interview explanation

  1. What is the grain of the input and output at each join?
  2. Which clock defines eligibility, and when is a report considered final?
  3. How would you investigate a slow correct query without changing its meaning?

Use the experiment as an example. State the failure, explain the chosen boundary, show the test and name the tradeoff. Keep measured results separate from production assumptions.

Read the primary documentation

Rebuy’s staff role calls for accurate experimentation reports, data models and analytical queries.

Related role from the job review

Choose your next step

Build the safeguards

Eight explained steps, a source example and a checkpoint at each decision.

Read the tutorial

Compare the alternatives

Understand where each pattern helps and which tradeoffs remain.

Compare approaches

Run and extend it locally

Download the independent environment, run the native checks and try a specific extension.

Open the how-to guide