PRACTICAL GUIDE

Build a SQL revenue report that handles refunds

Turn a business question into a sequence

The project asks a narrow question: how much net revenue did paid orders produce, and in which countries? Start by deciding what an eligible row is, then define the measure, then choose the grouping.

1. Select the relevant rows

Cancelled orders are excluded. A fully refunded paid order still belongs to the paid set, with net revenue of zero. The status filter and refund calculation serve different purposes.

2. Define the measure

net revenue for a row = revenue - refund

Summing revenue alone ignores refunds. Subtracting refunds both before and after aggregation counts them twice. The exercise uses non-null values in one currency; a real dataset also needs explicit decisions about tax, currencies, chargebacks and missing data.

3. Group and order

Group paid rows by country and sum their net amount. Name the calculated column net_revenue. Sort by that value descending; use country ascending for ties to make the result deterministic.

Check another dataset

A query that returns one correct total by coincidence is weak evidence. Each exercise runs against two fictional datasets. That catches some hardcoded answers and refund mistakes, but does not prove correctness on all possible data. Download the SQL and add cases of your own.

Keep learning

For more syntax practice, try SQLBolt. For the exact SQLite behavior used here, read the SQLite SELECT documentation. Our original exercise is a small project, not a complete course or certification.