Favour Nwabuisi

Problem

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

What I did

A single GROUP BY status query with COUNT(*) gave a complete frequency table across all statuses. Ordering the result by order_count DESC meant the most common statuses appeared first, making the output immediately scannable without any post-processing. The query was kept short deliberately, because at this stage the goal was orientation, not analysis.

Outcome

Nura had a clean, ranked status inventory to anchor the rest of the investigation and a first signal that roughly 4,110 orders carry reversal statuses relevant to net revenue.

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

Groups every row in the orders table by its status value and counts occurrences, ordering the result so the highest-volume statuses appear first.

Next step

A useful follow-up would be to break the reversal statuses down by month to check whether cancellations and returns spike in particular periods, which would tell Nura whether the gap is seasonal or structural.

Problem

Still supporting Nura Ibrahim's investigation at Meridian Retail, the next step was to establish an anchor figure before any status filtering: total order count, the full date range of the dataset, and gross revenue in naira.

What I did

Four aggregate functions were combined in one SELECT: COUNT(*) for total rows, MIN and MAX on created_at cast to date to strip the time component, and SUM(total_cents) divided by 100.0 wrapped in ROUND to produce a clean naira figure. Dividing by 100.0 rather than 100 ensures the result is a decimal, avoiding integer truncation. The table was referenced as retail.orders explicitly so the query would not depend on the session search path.

Outcome

Nura had a single, verified baseline row to use as the anchor before applying any status exclusions in subsequent queries.

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

Produces four summary metrics in one pass: row count, date boundaries with the time component stripped, and gross revenue converted from cents to naira with two decimal places.

Next step

The next useful step would be to rerun the same four metrics filtered to non-reversal statuses only, producing a net revenue baseline that can be compared directly against the gross figure already established.

Problem

Nura Ibrahim at Meridian Retail needed to resolve a conflict between two internal systems before a board presentation. The Finance system reported NGN 38.5M for October 2023 while the sales dashboard showed NGN 56.9M, and she needed both numbers explained and verified in a single result.

What I did

The query groups October 2023 orders by status and calculates three columns for each: gross revenue (all orders), net revenue using a CASE expression to zero out cancelled, returned, and refunded amounts, and excluded revenue using the inverse CASE to make the gap explicit. A safe date boundary, created_at >= '2023-10-01' AND created_at < '2023-11-01', captures all October rows without edge-case risk from timestamps. Adding the excluded_revenue_naira column as a third measure was a deliberate choice to make the reconciliation self-explanatory without requiring any mental arithmetic from a board-level reader.

Outcome

Nura could present both figures to the board with a clear explanation of the NGN 18.4 million difference, supported by a query that shows the source of every naira in each total.

ROUND(
 SUM(
 CASE
 WHEN status NOT IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END
 ) / 100.0, 2
) AS net_revenue_naira

Uses a CASE expression inside SUM to include only non-reversal order amounts before converting from cents to naira, producing the net revenue figure that matches the Finance system.

Next step

It would be worth running the same gross-versus-net reconciliation for every month in 2023 to check whether October's exclusion rate is typical or an outlier that warrants further investigation.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was receiving three different customer counts from different internal teams, 2,331, 2,131, and 2,200, before an investor meeting and needed one defensible number with a clear explanation of why the others were wrong.

What I did

All three counts were surfaced in a single SELECT using SUM(CASE WHEN ... END) to conditionally count rows meeting each criterion. The first measure counts every record with COUNT(*). The second applies is_duplicate = FALSE as the filter, treating that column as the authoritative deduplication flag. The third adds a further condition that the email field is not null. Writing all three in one query meant the relationship between the counts was visible in a single result row, which made the explanation to Chidi straightforward.

Outcome

Chidi could walk into the investor meeting with a single, sourced number and a one-sentence explanation of why the other figures exist.

Next step

A follow-on query examining the duplicate records themselves, for example, whether they share email addresses, phone numbers, or registration dates, would help explain how the duplicates were introduced and whether the 200 affected customers can be cleanly merged.

Problem

Nura Ibrahim at Meridian Retail needed a status-level revenue breakdown showing both gross and net totals, and the explicit difference between them, to understand where an NGN 18.4 million gap between two reporting systems was coming from.

What I did

The query grouped orders by status and summed total_cents divided by 100 for each group, ordering the output by gross revenue descending so the largest contributors appeared first. Converting from cents to naira and rounding to two decimal places produced figures in a usable format for Nura. The submission correctly surfaced all seven statuses and established that delivered orders dominate gross revenue.

Outcome

The per-status gross breakdown gave Nura a ranked view of revenue contribution by status, providing useful context even though the net-versus-gross comparison she needed was completed in a later query.

SELECT
 status AS order_status,
 COUNT(*) AS total_orders,
 ROUND(SUM(total_cents)/100, 2) AS gross_revenue_naira
FROM orders
GROUP BY status
ORDER BY gross_revenue_naira DESC;

Groups all orders by status and computes a count and gross revenue for each group, ordering by revenue so the highest-value statuses appear at the top of the result.

Next step

The missing piece here was a CASE-based net revenue column alongside the gross figure; adding that in a revised query, as done in mer-sql-j03, would have completed Nura's reconciliation in a single submission.