Ifiemi John-Lewis

Problem

Nura Ibrahim, CFO at Meridian Retail (simulated retailer), flagged a revenue discrepancy and needed a reliable picture of the orders table before any filtering began. The first question was straightforward: what order statuses exist, and how many orders carry each one?

What I did

Before any investigative filtering made sense, a clean status inventory was needed. I queried retail.orders with GROUP BY status and COUNT(*) to count every order against its status value. Sorting by order_count DESC made the output immediately readable from the most common status downward, which is the natural reading order for an executive scanning the result.

Outcome

Nura received a complete, ranked status map that confirmed no statuses were missing and gave her a reliable starting point for the revenue investigation.

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

This query groups every row in retail.orders by its status value and counts them, returning all 7 statuses ranked from most to least common in a single scan.

Next step

A useful next step would be to cross-tabulate status against the order creation year to see whether the mix of cancelled or refunded orders has shifted over the four-year date range.

Problem

Still supporting Nura Ibrahim's investigation at Meridian Retail, I was asked to produce a single-row baseline: total order count, the full date range of orders, and total revenue in naira. This summary would anchor all subsequent filtering and comparison.

What I did

Four metrics were needed in one statement. I used COUNT(*) for total orders, MIN and MAX on created_at cast to date with ::date to strip unnecessary time components, and SUM(total_cents) divided by 100.0 with ROUND to two decimal places to convert the stored cent values into a clean naira figure. Descriptive aliases were chosen to match the language of the business request exactly, so the output column names were self-explanatory without additional annotation.

Outcome

Nura had a verified, single-row baseline to anchor every subsequent filter and comparison in the investigation.

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 removes the time component from the timestamp columns, and dividing total_cents by 100.0 before rounding converts the stored integer cent values into a readable naira figure.

Next step

Breaking this same summary down by calendar year would show whether revenue growth, order volume, or both are driving the overall total.

Problem

Chidi Okafor, a stakeholder at Meridian Retail (simulated retailer), needed a channel-by-channel customer count to answer expected investor questions about acquisition strategy. The requirement was that duplicate customer records be excluded so the counts reflected real acquired customers.

What I did

The deduplication step was the critical filter: without WHERE is_duplicate = FALSE, channel counts would be inflated by flagged duplicate records. After applying that filter, GROUP BY acquisition_source and COUNT(*) produced the per-channel totals. Sorting by customer_count DESC puts the highest-volume channels at the top, which is the natural order for an investor-facing breakdown. A comment block at the top of the query documents the deduplication logic so any reviewer can understand the intent without running the query.

Outcome

Chidi received a clean, ranked channel breakdown built on deduplicated records, giving him a defensible acquisition story for the investor meeting.

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 removes duplicate-flagged rows before aggregation, so each channel count reflects only distinct acquired customers rather than raw record volume.

Next step

Adding average order value or total revenue per acquisition channel would let Chidi show not just which channels bring the most customers but which bring the most valuable ones.

Problem

Nura Ibrahim at Meridian Retail needed to understand the source of a flagged revenue discrepancy for October 2023. The question was precise: show gross revenue across all statuses, net revenue excluding cancelled, returned, and refunded orders, and the gap between them, all in a single result with per-status and grand-total rows.

What I did

GROUP BY ROLLUP(status) produces both the per-status rows and a grand-total row in one pass, so Nura could see both levels of the summary without a UNION ALL. For net revenue, I used the FILTER WHERE aggregate syntax to exclude the three problem statuses directly inside SUM, which is cleaner than wrapping a CASE inside the sum. The revenue gap was isolated with CASE WHEN inside SUM, and the date range used >= and < rather than BETWEEN to avoid cutting off late-night orders on 31 October. Sorting by gross_revenue DESC NULLS LAST keeps the grand-total row from floating to the top.

Outcome

Nura received a single query result showing both per-status revenue and the overall rollup, confirming the exact source and size of the discrepancy she had flagged.

SELECT
 SUM(total_cents) / 100.0 AS gross_revenue,
 COALESCE(SUM(total_cents) FILTER (
 WHERE status NOT IN ('cancelled', 'returned', 'refunded')
 ) / 100.0) AS net_revenue,
 SUM(CASE WHEN status IN ('cancelled', 'returned', 'refunded')
 THEN total_cents ELSE 0 END) / 100.0 AS revenue_gap
FROM retail.orders
WHERE created_at >= '2023-10-01'
 AND created_at < '2023-11-01'
GROUP BY ROLLUP(status)
ORDER BY gross_revenue DESC NULLS LAST;

The FILTER WHERE clause computes net revenue conditionally inside the SUM without a subquery, and ROLLUP(status) generates the grand-total row alongside per-status rows in a single aggregation pass.

Next step

Comparing this October breakdown against the same status split for September and November would show whether the gap is seasonal or specific to that month.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was walking into an investor meeting with three different customer counts in circulation: 2,331, 2,131, and 2,200, and needed one number he could defend. The task was to explain where each count came from and identify which was correct.

What I did

Rather than writing three separate queries, I used COUNT(*) FILTER WHERE to compute all five headline figures in a single table scan: total rows, non-duplicate customers, non-duplicates with a confirmed email, non-duplicates with at least one order, and non-duplicates with zero orders. A detailed comment block above the SQL narrates the business logic behind each figure so Chidi could read the explanation before the numbers. For the unresolvable 2,200 figure, the honest response was to flag it as outside the available filters and recommend Chidi go back to the originating team rather than speculate.

Outcome

Chidi received a single query with a plain-language explanation of all three counts and a clear recommendation on which figure to use, along with a flagged action item to resolve the unexplained 2,200.

SELECT
 COUNT(*) AS total_rows,
 COUNT(*) FILTER (
 WHERE is_duplicate = FALSE
 ) AS non_duplicate_customers,
 COUNT(*) FILTER (
 WHERE is_duplicate = FALSE
 AND total_orders > 0
 ) AS active_non_duplicates,
 COUNT(*) FILTER (
 WHERE is_duplicate = FALSE
 AND total_orders = 0
 ) AS non_duplicates_with_zero_orders
FROM retail.customers;

Multiple FILTER WHERE clauses on a single COUNT allow all five customer segments to be computed in one scan, so each figure shares the same source row set and the differences between them are directly comparable.

Next step

Pulling the registration dates for the 26 zero-order non-duplicate customers would show whether they are genuinely inactive or simply very recent acquisitions who have not yet purchased.