John Haastrup

Problem

Nura Ibrahim (CFO) at Meridian Retail (simulated retailer) flagged a revenue discrepancy and needed a clean starting point before any filtering began. The specific question was: what order statuses exist in the orders table, and how many orders carry each one? Understanding the full status landscape was a prerequisite for every reconciliation step that followed.

What I did

A single aggregation query using GROUP BY status with COUNT(*) aliased as order_count gave a precise enumeration of every status present in the table. Ordering the result by order_count DESC meant the most frequent status surfaced immediately, making the output readable without any post-processing. Because the retail search path was already set in the session, no schema prefix was required, keeping the query concise.

Outcome

Nura received a complete, ranked status map of the orders table, giving her a reliable foundation before any revenue filters were applied.

SELECT 
 status,
 COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

This query groups every row in the orders table by its status value and counts them, then sorts the result so the highest-volume status appears first.

Next step

A useful next step would be to break the delivered count down by month to check whether the status distribution has shifted over the four-year date range.

Problem

Before any analysis could begin on the Meridian Retail (simulated retailer) dataset, Nura Ibrahim asked me to familiarise myself with the raw Excel file and document what was in it. The task required identifying all sheets, listing the columns in the Orders sheet, enumerating distinct order statuses, and flagging at least three genuine data quality issues.

What I did

Working through each of the six sheets systematically, I recorded sheet names, purposes, and column listings on a dedicated Answer sheet. For order statuses, I used Excel to identify all 7 distinct values and confirmed their counts. Two specific quality flags came from close inspection: the legacy_order_ref column in Orders was blank across all 1,849 rows, and the Inventory sheet contained negative stock_qty values, both of which indicate problems that would corrupt downstream calculations. I also noted that cancelled, returned, and refunded orders would need to be excluded from any net revenue figure.

Outcome

The structured audit sheet gave the investigation team a documented starting inventory of the data and a prioritised list of issues to resolve before modelling began.

Next step

The two issues I missed, category inconsistency in Order_Items and duplicate customers in Customers, are the next things to hunt for in a follow-up pass, since both would affect any customer-level or product-level rollup.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed a single-row baseline summary of the orders table before any filtering was applied. The request covered four metrics: total order count, earliest and latest order dates, and total revenue expressed in naira rather than the stored cents unit.

What I did

All four metrics were handled in one SELECT statement against retail.orders. Using MIN(created_at)::date and MAX(created_at)::date cast the timestamp values to clean dates, removing the time component that would have cluttered a stakeholder report. Revenue conversion used SUM(total_cents) / 100.0 inside a ROUND call to two decimal places, because the cents-to-naira division on an integer column requires the explicit decimal divisor to avoid integer truncation. Descriptive aliases, total_orders, earliest_order, latest_order, and total_revenue_naira, were chosen so the output column headers would be self-explanatory without a data dictionary.

Outcome

Nura had a single verified baseline row to anchor the reconciliation, making it straightforward to compare any filtered subset against the full dataset total.

SELECT
 COUNT(*) AS total_orders,
 MIN(created_at)::date AS earliest_order,
 MAX(created_at)::date AS latest_order,
 ROUND(SUM(total_cents) / 100.0, 2) AS total_revenue_naira
FROM retail.orders;

The ::date cast strips the timestamp, and dividing by 100.0 rather than 100 forces decimal arithmetic so the sum is not truncated before rounding.

Next step

Splitting this baseline by calendar year would show whether revenue growth across the four-year window is linear or concentrated in specific periods.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a clean breakdown of how many customers came from each acquisition channel ahead of an investor conversation about acquisition strategy. The request specified that duplicate customer records should be excluded to avoid inflating any channel's count.

What I did

Filtering with WHERE is_duplicate = FALSE removed the 200 flagged duplicate records before any counting occurred, so COUNT(*) operated only on the 2,131 unique customers. GROUP BY acquisition_source then partitioned those customers by channel, and ORDER BY customer_count DESC produced the ranked list Chidi needed. Because the duplicate filter was applied at the row level, COUNT(*) was appropriate and carried no risk of undercounting.

Outcome

Chidi received a ranked, duplicate-free channel breakdown ready to cite in the investor discussion without manual adjustments.

SELECT
 acquisition_source,
 COUNT(*) AS customer_count
FROM retail.customers
WHERE is_duplicate = FALSE
GROUP BY acquisition_source
ORDER BY customer_count DESC;

The WHERE clause filters before aggregation, so the COUNT reflects only non-duplicate customers, and the ORDER BY sorts channels from highest to lowest volume.

Next step

Comparing average order value or lifetime revenue by acquisition channel would tell Chidi not just which channel is largest but which is most valuable.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) found that the Finance system and the sales dashboard showed different October 2023 revenue totals, NGN 38.5 million versus NGN 56.9 million. Before presenting to the board, she needed a breakdown of October orders by status showing both gross revenue and net revenue after discounts, so the source of the gap could be explained clearly.

What I did

The date range was filtered using a half-open interval on created_at, greater than or equal to 2023-10-01 and less than 2023-11-01, which avoids any boundary edge cases that an inclusive upper bound on a timestamp column can introduce. Joining order_items allowed gross revenue to be calculated as SUM(quantity * unit_price_cents) / 100.0, and wrapping the discount column in COALESCE(oi.discount_cents, 0) prevented NULLs from propagating into the net revenue calculation. A custom ORDER BY CASE sorted statuses in a revenue-funnel sequence, delivered first through refunded last, so the board output read in a logical business order rather than alphabetically.

Outcome

Nura received an auditable, status-by-status table showing exactly where the gap between the two systems came from, formatted so it could be presented to the board without further editing.

SELECT
 o.status,
 COUNT(DISTINCT o.order_id) AS order_count,
 SUM(oi.quantity * oi.unit_price_cents) / 100.0 AS gross_revenue_naira,
 SUM(COALESCE(oi.discount_cents, 0)) / 100.0 AS discount_naira,
 SUM(
 (oi.quantity * oi.unit_price_cents)
 - COALESCE(oi.discount_cents, 0)
 ) / 100.0 AS net_revenue_naira
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2023-10-01 00:00:00'
 AND o.created_at < '2023-11-01 00:00:00'
GROUP BY o.status
ORDER BY CASE o.status WHEN 'delivered' THEN 1 WHEN 'shipped' THEN 2 WHEN 'confirmed' THEN 3 WHEN 'pending' THEN 4 WHEN 'returned' THEN 5 WHEN 'cancelled' THEN 6 WHEN 'refunded' THEN 7 ELSE 8 END;

COALESCE guards against NULL discounts, the half-open date interval avoids timestamp boundary issues, and the CASE block produces a stakeholder-readable row order rather than an arbitrary one.

Next step

Running the same gross-versus-net breakdown across every month in 2023 would show whether the discount gap in October is typical or an outlier worth investigating further.