Regional Order Report: Inflated Counts, Missing Regions, Overstated Revenue
FleetOps runs its order analytics on Oracle 19c. ORDERS has order_id (primary key), customer_id, order_date DATE and status VARCHAR2(1), where 'N' is new, 'S' shipped, 'P' pending and 'X' cancelled. ORDER_ITEMS has order_id, line_no, product_id, qty, unit_price NUMBER(8,2) and discount_pct NUMBER(5,2) stored as a percentage (0-100, nullable). CUSTOMERS has customer_id and region VARCHAR2(20), which is NULL for self-service signups. Orders average 3.1 item lines. The monthly report is:
SELECT c.region, COUNT(*) AS order_count, SUM(oi.qty * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN customers c ON c.customer_id = o.customer_id WHERE o.order_date >= DATE '2024-01-01' GROUP BY c.region;
For NORTH it returns 4,812 orders while the order-entry dashboard shows 1,604. Revenue is 6.2% above the finance ledger. Two small regions do not appear at all, and one output row has a blank region. Call-centre orders are created as 'P' before any line is added. Finance reports net of discount and excludes cancelled orders.
1. Explain both the inflated order count and the two missing regions, then rewrite the query so every order is counted exactly once, pending orders with no lines are still counted, and NULL regions are labelled.
Read the worked answer
Why 4,812. COUNT(*) counts rows of the joined result, not orders. The ORDERS-to-ORDER_ITEMS join is one-to-many, so each order expands to about 3.1 rows: 1,604 x 3.1 is roughly 4,812. The fix is to count a key that is unique per order, not rows: COUNT(DISTINCT o.order_id). COUNT(oi.order_id) would recreate the bug, because it also counts item lines.
Why two regions vanished. The inner join to ORDER_ITEMS discards any order that has no line yet. If those small regions only contain 'P' pending call-centre orders, every row for them is eliminated before grouping. Switching to LEFT JOIN order_items keeps the order row and produces NULL item columns for it.
Why one row is blank. The grouping column itself is NULL for self-service customers. NULL is a valid group, so the row exists but has no label; NVL(c.region,'UNASSIGNED') fixes the display, and the same expression must appear in GROUP BY.
SELECT NVL(c.region,'UNASSIGNED') AS region,
COUNT(DISTINCT o.order_id) AS order_count
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2024-01-01'
GROUP BY NVL(c.region,'UNASSIGNED');
Note the cost: COUNT(DISTINCT) needs a sort or hash of the order keys. On very large volumes, pre-aggregating ORDER_ITEMS in an inline view on order_id and joining that one-row-per-order result is cheaper.
2. Revenue is 6.2% higher than the ledger. Identify the causes and give the corrected aggregate expression, including the handling the LEFT JOIN now requires.
Read the worked answer
Two independent omissions explain the gap. First, SUM(oi.qty * oi.unit_price) is gross; it never applies discount_pct. Second, the WHERE clause filters only on date, so cancelled orders (status 'X') are still summed even though finance reverses them.
The corrected expression must also survive the LEFT JOIN: for an order with no lines, the item columns are NULL, and NULL * NULL is NULL. SUM ignores NULLs, so the order contributes nothing to revenue (which is correct) but NVL makes the intent explicit and protects against a NULL unit_price in partially loaded lines.
SELECT NVL(c.region,'UNASSIGNED') AS region,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(NVL(oi.qty,0) * NVL(oi.unit_price,0)
* (1 - NVL(oi.discount_pct,0)/100)) AS net_revenue
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2024-01-01'
AND o.status <> 'X'
GROUP BY NVL(c.region,'UNASSIGNED');
Beware of the arithmetic itself: discount_pct/100 with a NUMBER column is exact, but if the column were BINARY_FLOAT the result would drift. Also check whether the ledger uses status 'S' only; if so, status = 'S' is the safer predicate, because <> 'X' silently includes new and pending orders.
Validate by reconciling one region to the cent before running the whole report.
3. The analyst now needs only regions whose net revenue exceeds 1,000,000, plus a count of distinct customers per region. Where does the predicate belong and why? Give the statement.
Read the worked answer
The revenue filter is a group-level condition, so it belongs in HAVING, not WHERE. WHERE is evaluated before grouping and can only reference single rows; putting SUM(...) > 1000000 there raises ORA-00934 (group function is not allowed here).
A second trap: Oracle does not allow a SELECT-list alias in HAVING, even though it does allow one in ORDER BY. The aggregate expression must be repeated verbatim, or the whole query wrapped in an inline view and filtered outside.
SELECT NVL(c.region,'UNASSIGNED') AS region,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT o.customer_id) AS customer_count,
SUM(NVL(oi.qty,0) * NVL(oi.unit_price,0)
* (1 - NVL(oi.discount_pct,0)/100)) AS net_revenue
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2024-01-01'
AND o.status <> 'X'
GROUP BY NVL(c.region,'UNASSIGNED')
HAVING SUM(NVL(oi.qty,0) * NVL(oi.unit_price,0)
* (1 - NVL(oi.discount_pct,0)/100)) > 1000000
ORDER BY net_revenue DESC;
Row-level predicates such as the date range and the status stay in WHERE so they reduce rows before aggregation; HAVING only trims groups afterwards. If the expression is long or used repeatedly, the inline-view form (SELECT * FROM (...) WHERE net_revenue > 1000000) is more maintainable and avoids repeating the formula in two places.