Quick summary
Summarize this blog with AI
E-commerce data science interviews often test whether you understand the business quantity and the data grain before writing a query. This original case uses synthetic order lines and refunds to show how a plausible join can double-count money.
Define the quantity before calculating
For this exercise, gross merchandise amount is the sum of line amounts for paid orders. Net merchandise amount subtracts the recorded refunds for those orders. It excludes taxes, shipping, fees, discounts not already reflected in line amounts, and any accounting treatment beyond this stated definition. It is not automatically recognized accounting revenue or profit.
Order 101 has two lines worth 60 and 40, with two refunds worth 5 and 15. Order 102 has one line worth 80 and no refunds. Order 103 is cancelled and has a line worth 60. The expected paid-order gross amount is 180, refunds are 20, and net merchandise amount is 160.
Why does joining raw lines to raw refunds fail?
Order 101 has two lines and two refunds. A direct join by order ID creates four rows: each line appears twice and each refund appears twice. Summing that joined result would report gross 200 and refunds 40 for order 101, instead of 100 and 20. COUNT(DISTINCT order_id) does not fix duplicated monetary sums.
Aggregate each many-row relation to one row per order before joining. Check that the orders relation has one row per order as well. This is a grain problem, not a reason to sprinkle DISTINCT over every expression.
Write a complete PostgreSQL solution
WITH orders(order_id, status) AS (
VALUES (101, 'paid'), (102, 'paid'), (103, 'cancelled')
), lines(order_id, line_id, amount) AS (
VALUES (101, 1, 60::numeric), (101, 2, 40::numeric),
(102, 1, 80::numeric), (103, 1, 60::numeric)
), refunds(order_id, refund_id, amount) AS (
VALUES (101, 1, 5::numeric), (101, 2, 15::numeric)
), line_totals AS (
SELECT order_id, SUM(amount) AS gross
FROM lines GROUP BY order_id
), refund_totals AS (
SELECT order_id, SUM(amount) AS refunded
FROM refunds GROUP BY order_id
)
SELECT o.order_id, l.gross, COALESCE(r.refunded, 0) AS refunded,
l.gross - COALESCE(r.refunded, 0) AS net_merchandise
FROM orders AS o
JOIN line_totals AS l ON l.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
WHERE o.status = 'paid'
ORDER BY o.order_id;
Expected rows are (101, 100, 20, 80) and (102, 80, 0, 80). Order 103 is excluded. COALESCE treats the absence of refund records as zero under this fixture’s contract. A missing or incomplete refund feed is different and must not silently become zero.
The exercise assumes unique order/line/refund identifiers, nonmissing valid amounts, and at least one line for every paid order. Validate those assumptions before running this inner join in a real pipeline, or explicitly report invalid orders rather than losing them. Check orphan refunds, repeated refund events, currency, and cases where refunds exceed the defined gross amount.
How do refund timing and conversion affect the analysis?
An order cohort’s eventual refunds may arrive after its purchase date. Comparing recent cohorts with mature cohorts without the same follow-up window can make recent performance look better. State whether the report groups cash movements by event date or attributes refunds back to purchase cohorts; those answer different questions.
For conversion, define the eligible visitor or user population and a matching outcome window. Completed orders divided by page views is not the same metric as the fraction of eligible users who place at least one order. Distinguish zero purchases from missing tracking, and avoid attributing cross-device identity matches without an appropriate identity policy.
What would you recommend to the business?
If net merchandise amount increases, ask whether order volume, basket size, product mix, prices, or refunds changed. Profit requires relevant costs; the query does not contain them. A promotion can raise sales while reducing contribution margin.
For a proposed recommendation or promotion change, define a controlled test, unit of assignment, primary outcome, and guardrails such as margin, refunds, delivery failures, and customer complaints. Account for inventory constraints and spillovers. An observational correlation between recommendation clicks and purchasing does not establish incremental purchases.
Practice by adding a third refund to order 101 and verifying that its gross stays 100. Then remove its refunds and verify net becomes 100. Explain which data-contract failure would make those results misleading. That combination of query, fixture, and business interpretation is more useful than memorizing a list of e-commerce metrics.
