Toluwani Adefila

Problem

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

What I did

Before any revenue filtering could happen, the status taxonomy needed to be confirmed from the data itself rather than assumed. A GROUP BY query on the status column with COUNT(*) gave every distinct value and its frequency in one pass. Ordering by order_count descending meant the CFO could read the output top-to-bottom in priority order without any post-processing. Schema qualification on retail.orders and a clean alias on the count column were chosen to keep the query portable and the output self-explanatory.

Outcome

The output gave the CFO a verified, priority-ordered status list to use as the foundation for subsequent revenue reconciliation queries.

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

Groups every row in retail.orders by its status value and counts occurrences, then sorts highest-count statuses first so the most operationally significant categories are immediately visible.

Next step

A useful follow-on would be breaking the status counts down by quarter to see whether the mix of cancelled or returned orders has shifted over the four-year date range.

Problem

Continuing Nura Ibrahim's investigation at Meridian Retail, the next step was establishing a baseline before any filtering was applied. Nura needed four metrics in a single result: total order count, the earliest and latest order dates, and total gross revenue converted from stored cents to naira.

What I did

All four metrics were computed in a single SELECT with no GROUP BY, so the result is one summary row covering the entire orders table. MIN and MAX on the created_at timestamp were cast to date using the ::date operator, which strips the time component and produces a clean calendar date. Revenue conversion required dividing total_cents by 100.0, using the decimal literal rather than the integer 100 to force a floating-point result and avoid truncation. ROUND with two decimal places then brought the naira figure to a standard currency format.

Outcome

The single-row summary gave Nura a confirmed, unfiltered baseline to anchor all subsequent comparisons in the revenue 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;

Computes all four baseline metrics in one table scan: order count, date boundaries via MIN and MAX with ::date casting, and gross revenue via cents-to-naira conversion with decimal precision enforced by dividing by 100.0.

Next step

Breaking this same summary down by year would show whether order volume and revenue have grown, declined, or fluctuated across the four-year window, which would add context to any single-period anomaly Nura finds.

Problem

Nura Ibrahim at Meridian Retail needed to resolve a specific conflict before a board presentation: the Finance system reported October 2023 revenue as approximately NGN 38.5 million while the sales dashboard showed approximately NGN 56.9 million. The question was which figure was correct, why they differed, and what the reconciling item was.

What I did

Two revenue columns were computed in the same query, one summing all orders regardless of status (gross), and one excluding cancelled, returned, and refunded orders (net). The date filter used a half-open boundary, created_at greater than or equal to 2023-10-01 and strictly less than 2023-11-01, which safely captures every order in October including those timestamped at midnight on the 31st. For the net column, a FILTER (WHERE status NOT IN ...) aggregate was used rather than a CASE expression, keeping the SQL concise and the intent explicit. COALESCE wraps the filtered SUM so that statuses with no qualifying rows display zero rather than null, making the output readable without further formatting.

Outcome

The status-level breakdown gave Nura a single query output she could present to the board showing exactly which order types account for the difference between the two reported figures.

SELECT status,
COUNT(*) AS order_count,
SUM(total_cents)/100.0 AS gross_revenue, 
COALESCE( SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded')) / 100.0, 0 ) AS net_revenue_naira
FROM retail.orders 
WHERE created_at>= '2023-10-01'
 AND created_at < '2023-11-01'
GROUP BY status;

The FILTER aggregate inside COALESCE computes net revenue in a single pass by excluding the three problem statuses, while the half-open date boundary ensures no October order is missed or double-counted.

Next step

Running the same gross-versus-net split for every month in 2023 would show whether October's cancellation and return volumes are unusual or part of a recurring seasonal pattern.

Problem

Chidi Okafor (simulated Head of Strategy at Meridian Retail) was receiving three different customer counts from three internal teams ahead of an investor meeting: 2,331 from Marketing, 2,131 from Operations, and 2,200 from Finance. He needed one number he could defend, with an explanation of why the other counts were wrong.

What I did

All three counting methods were surfaced in a single query using COUNT(*) with conditional FILTER clauses, so the output is one row showing exactly what each team was doing. The raw COUNT(*) reproduces Marketing's figure by counting every row including duplicates. Adding FILTER (WHERE is_duplicate = false) reproduces Operations' figure by relying on a system-generated boolean flag to remove known duplicate records. A third column adds an email IS NOT NULL condition on top of the deduplication filter, which is what Finance claimed to use. Running all three in one pass makes the comparison unambiguous and avoids the risk of inconsistent results from separate queries.

Outcome

Chidi received a single-query output showing all three counting methods side by side, with a clear explanation of why 2,131 is the number to cite and how the other figures arise.

SELECT
COUNT(*) AS total_no_of_customers,
COUNT(*) FILTER (WHERE is_duplicate = false) AS non_duplicate_total_no_of_customers,
COUNT(*) FILTER (WHERE is_duplicate = false AND email IS NOT NULL) AS non_duplicate_email_no_of_customers
FROM retail.customers

Three FILTER clauses on a single COUNT(*) reproduce each team's counting method in one table scan, making the source of each discrepancy immediately visible without requiring separate queries or subqueries.

Next step

Looking at the 200 duplicate records more closely, specifically whether they share email addresses, names, or registration dates, would help determine whether the duplication is a data entry issue or a system import artifact.

Problem

Nura Ibrahim at Meridian Retail needed to understand the composition of the revenue gap between gross and net figures. The question was which order statuses were responsible and how large each contribution was, so the gap could be explained clearly rather than reported as a single unexplained number.

What I did

The query groups all orders by status and computes three columns: gross revenue, net revenue, and a revenue gap column equal to their difference. Net revenue uses a FILTER aggregate that returns null for the excluded statuses, which accurately reflects that cancelled, returned, and refunded orders contribute nothing to net revenue. Rather than masking those nulls with COALESCE, the null output was left as-is for the net column, while the gap column is derived by subtracting the filtered SUM from the gross SUM directly. Dividing by 100.0 at each step maintains decimal precision throughout. The reviewer noted the gap column as a useful addition because it makes the per-status contribution to the total discrepancy visible in one glance.

Outcome

The row-level gap column let Nura see each problem status and its exact contribution to the discrepancy, giving her a structured breakdown to bring to Finance rather than a single aggregate figure.

SELECT 
 status, 
 SUM(total_cents)/100.0 AS Gross_revenue,
 SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded'))/100.0 AS net_revenue,
 (SUM(total_cents) - SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded'))) / 100.0 AS revenue_gap
FROM 
 retail.orders
GROUP BY 
 status;

The revenue_gap column is computed inline by subtracting the filtered SUM from the full SUM, so each row shows how much that status contributes to the difference between gross and net revenue without requiring a separate calculation step.

Next step

Applying the same status-level breakdown with a date filter scoped to a single month would let Nura confirm whether the NGN 18.4 million gap she referenced maps specifically to October 2023 or spans the full dataset.