Analyze Ecommerce Orders and Refunds With SQL

Know what one row means.

orders

Key · order_id

One row per placed order.

order_items

Key · order_item_id

One row per order line.

refunds

Key · refund_id

One row per successful refund event; an order can have several partial refunds.

monthly_sales

Key · order_month

One row per UTC order month.

Fix the meaning before the code.

Gross sales

Quantity multiplied by order-item unit price for completed, partially refunded, and refunded orders before line discounts and refunds. Canceled orders contribute zero.

Discounts

The sum of order_items.discount_amount for included orders, attributed to the order month.

Refunded amount

All refund events attached to a completed order, attributed back to the order month even when processed later.

Net sales

Gross sales minus discounts and refunds. Tax, shipping, failed payments, and chargebacks are not included.

Work from source grain to tested output.

1. 01

   Collapse refunds to the order grain

   Refund events are more granular than orders. Aggregate first so joining them cannot repeat order revenue.

   WITH refunds_by_order AS (
     SELECT
       order_id,
       SUM(amount) AS refunded_amount
     FROM refunds
     GROUP BY order_id
   )
   SELECT *
   FROM refunds_by_order
   ORDER BY order_id;

   Verification

   • The result is unique on order_id.
   • An order with two partial refunds has one row containing their sum.
2. 02

   Join at compatible grains and aggregate

   Join one refund total to each completed order, then roll the order ledger up to calendar month.

   WITH items_by_order AS (
     SELECT
       order_id,
       SUM(quantity * unit_price) AS gross_amount,
       SUM(discount_amount) AS discount_amount
     FROM order_items
     GROUP BY order_id
   ),
   
   refunds_by_order AS (
     SELECT
       order_id,
       SUM(amount) AS refunded_amount
     FROM refunds
     GROUP BY order_id
   ),
   
   order_ledger AS (
     SELECT
       substr(o.ordered_at, 1, 7) AS order_month,
       o.order_id,
       i.gross_amount,
       i.discount_amount,
       COALESCE(r.refunded_amount, 0) AS refunded_amount
     FROM orders AS o
     INNER JOIN items_by_order AS i
       ON o.order_id = i.order_id
     LEFT JOIN refunds_by_order AS r
       ON o.order_id = r.order_id
     WHERE o.order_status IN ('completed', 'partially_refunded', 'refunded')
   ),
   
   monthly_sales AS (
     SELECT
       order_month,
       ROUND(SUM(gross_amount), 2) AS gross_sales,
       ROUND(SUM(discount_amount), 2) AS discounts,
       ROUND(SUM(refunded_amount), 2) AS refunds,
       ROUND(SUM(gross_amount - discount_amount - refunded_amount), 2) AS net_sales
     FROM order_ledger
     GROUP BY order_month
   )
   
   SELECT *
   FROM monthly_sales
   ORDER BY order_month;

   Verification

   • Canceled orders do not contribute sales.
   • An order with no refund receives refunded_amount = 0.
   • Refund timing does not move revenue between order months.
3. 03

   Turn the grain and accounting rules into tests

   These zero-row checks catch duplicate output keys and arithmetic drift when the release changes.

   SELECT
     order_month,
     COUNT(*) AS row_count
   FROM monthly_sales
   GROUP BY order_month
   HAVING COUNT(*) > 1;

   SELECT *
   FROM monthly_sales
   WHERE ABS(gross_sales - discounts - refunds - net_sales) > 0.01;

   Verification

   • Both test queries return zero rows.
   • The 0.01 tolerance absorbs only the rounding applied to displayed currency amounts.

What this result does not claim.

• —Release v1.0.0 is USD-only. Mixed-currency releases must retain currency in the grain or convert before aggregation.
• —Refunds without a valid order_id cannot enter the reconciliation and should be quarantined separately.
• —Refunds are attributed to the original order month, not the month cash left the business.
• —Chargebacks, shipping, tax, and payment processing fees are outside this net-sales definition.
• —This item-based merchandise measure differs from the dataset page's checked payment-value example, which includes tax and shipping.

Open the matching dataset and exercise.