Loading…
Investigate a real month-over-month decline inside a deterministic ecommerce dataset. Submit a metric definition, reproducible analysis, validation checks, executive summary, and recommendation.
You support a fictional ecommerce company. The finance lead reports that September 2025 net payment value is lower than August and asks for an explanation before the monthly business review.
Your job is to verify the comparison, identify which measurable changes contribute to it, and separate supported findings from plausible explanations that the data cannot prove. The dataset is synthetic, but the ambiguity is intentional: orders, line items, payments, and refunds describe different events at different grains.
Both options contain the same versioned source tables. Use the SQLite database for direct SQL analysis or the CSV package for Python, spreadsheets, a warehouse, or another tool.
State the population, grain, date rule, successful-payment rule, refund treatment, currency, and limitations.
Provide readable SQL or a notebook that rebuilds the comparison and decomposes the change without duplicating values.
Show at least three checks, including a grain or uniqueness check, a reconciliation, and one edge-case inspection.
Write no more than 300 words: what changed, what the data supports, what remains uncertain, and what should happen next.
Create one chart or table that makes the comparison understandable without requiring the reviewer to read your code.
The candidate brief does not depend on this walkthrough. It is an instructor example for independent review after you have recorded your own definitions, checks, and conclusion.
This instructor example is not the only valid solution. Attempt the case first, then compare definitions, grain decisions, checks, and the limits of the conclusion.
Net payment value is successful payment amount minus linked refunds, assigned to the original order month after both money-event tables are aggregated to order grain.
Start with the supplied definition before testing explanations. This query is the versioned checked example from Ecommerce Sales & Returns v1.0.0.
WITH paid AS (
SELECT order_id, SUM(amount) AS paid_amount
FROM payments
WHERE payment_status = 'succeeded'
GROUP BY order_id
),
refunded AS (
SELECT order_id, SUM(amount) AS refunded_amount
FROM refunds
GROUP BY order_id
)
SELECT substr(o.ordered_at, 1, 7) AS order_month,
ROUND(SUM(COALESCE(p.paid_amount, 0) - COALESCE(r.refunded_amount, 0)), 2) AS net_sales
FROM orders o
LEFT JOIN paid p USING (order_id)
LEFT JOIN refunded r USING (order_id)
GROUP BY order_month
ORDER BY order_month;Practice the payment-and-refund calculation with a separate, smaller teaching fixture. It is not the take-home dataset.
The comparison follows the supplied definition, uses compatible grains, and reconciles to simpler checks.
The work tests multiple hypotheses and does not confuse association, timing, or synthetic patterns with causation.
Another analyst can run the work against dataset version 1.0.0 and understand each transformation.
The summary leads with the decision-relevant result, defines the measure, and keeps limitations visible.
The recommendation follows from the measured contribution and names the next evidence required.
Confirm that payment and refund aggregates each contain at most one row per order before joining. Reconcile their independent totals to the joined result.
WITH paid AS (
SELECT order_id, SUM(amount) AS paid_amount
FROM payments
WHERE payment_status = 'succeeded'
GROUP BY order_id
),
refunded AS (
SELECT order_id, SUM(amount) AS refunded_amount
FROM refunds
GROUP BY order_id
)
SELECT
COUNT(*) AS joined_orders,
ROUND(SUM(COALESCE(p.paid_amount, 0)), 2) AS paid_amount,
ROUND(SUM(COALESCE(r.refunded_amount, 0)), 2) AS refunded_amount
FROM orders AS o
LEFT JOIN paid AS p USING (order_id)
LEFT JOIN refunded AS r USING (order_id);Compare order count and payment value per qualifying order at order grain. Analyze product mix separately at item grain, then describe it as an association rather than adding item rows directly to the payment measure.
Lead with the reproduced 6.32% decline, name the largest measured contributors, keep the synthetic-data limitation visible, and request operational evidence before assigning a cause.