Yusuf Alarape Abiodun

Problem

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

What I did

A GROUP BY on the status column paired with COUNT(*) captures every distinct value in a single pass, without needing to know the statuses in advance. Ordering the result by the count descending meant the dominant status would surface immediately, reducing the time Nura needed to scan the output. The alias Total_order was chosen to make the column self-explanatory in a shared result set.

Outcome

Nura received a complete, ranked status inventory she could use as the foundation for all subsequent filtering in the revenue investigation.

SELECT status,
 count(*) as Total_order
FROM orders
GROUP BY status
ORDER BY Total_order DESC
;

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

Next step

A useful next step would be to calculate each status as a percentage share of total orders, so the relative weight of revenue-excluding statuses is immediately visible without manual arithmetic.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed a baseline snapshot of the entire orders table before any status or date filters were applied. The ask was four metrics in one result: total order count, earliest and latest order dates, and total revenue converted to naira.

What I did

Pulling all four metrics in a single SELECT avoids running separate queries that could return slightly different row counts if the table is live. Casting created_at to date with ::date strips the time component so the output reads cleanly as calendar dates. Dividing total_cents by 100.0 rather than the integer 100 forces floating-point arithmetic before ROUND is applied, which prevents truncation on the naira figure.

Outcome

Nura had a single, defensible baseline row to anchor all subsequent filtered comparisons in the investigation.

SELECT COUNT(*) AS Total_Order,
 MIN(created_at)::date AS Earliest_Order,
 MAX(created_at)::date AS Latest_Order,
 ROUND(SUM(total_cents)/100.0, 2) AS Total_Revenue
FROM orders
;

One SELECT returns all four baseline metrics in a single scan; dividing by 100.0 ensures the naira total is computed with decimal precision before rounding.

Next step

Breaking this same baseline query out by calendar year would show whether order volume and revenue are growing, flat, or declining across the four-year window.

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim needed to reconcile two October 2023 revenue figures before a board presentation: the Finance system showed approximately NGN 38.5M while the sales dashboard showed approximately NGN 56.9M. The ask was a per-status breakdown with both a gross and a net revenue column, computed in one query.

What I did

The date filter uses >= '2023-10-01' AND < '2023-11-01' rather than a BETWEEN or month extraction, which correctly captures every timestamp in October regardless of time-of-day without risk of boundary overlap. For the net column, the FILTER clause inside SUM restricts summation to statuses Finance counts as real revenue, delivering a zero contribution from cancelled, returned, and refunded rows in the same scan. Wrapping net with COALESCE guards against null in cases where all rows for a status are excluded. Dividing by 100.0 at the end of each expression keeps the decimal arithmetic correct before the value is returned.

Outcome

Nura could present the board with a documented, query-backed explanation of the discrepancy, showing it results from a definitional difference in which statuses each system counts, not a data error.

SELECT Status,
 SUM(total_cents)/100.0 AS Gross,
 COALESCE(SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded')), 0) /100.0 AS Net_Revenue
 FROM orders
 WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01'
 Group BY status
 ORDER BY Gross DESC
 ;

The FILTER clause inside SUM computes net revenue in a single pass by silencing the three excluded statuses, while COALESCE converts any resulting null to zero for clean output.

Next step

Running the same gross-vs-net split across all twelve months of 2023 would show whether October's gap ratio is typical or an outlier worth investigating further.

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim needed to see October 2023 revenue broken down by every order status, with both a gross column and a net column that excludes cancelled, returned, and refunded orders, so the gap between the two figures would be immediately visible.

What I did

Grouping by status and computing two SUM expressions in the same SELECT avoids a self-join and keeps the query easy to audit. The FILTER clause on the net column automatically returns null for excluded statuses, making the gap rows stand out without needing a separate CASE expression. The same safe date boundary pattern from earlier tasks keeps October scoped correctly. Dividing each SUM by 100.0 converts cents to naira with decimal accuracy.

Outcome

Nura received a per-status table that made the revenue gap visible at a row level, showing exactly which statuses contribute to the difference between gross and net.

SELECT SUM(total_cents)/100.0 AS Gross,
 SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded'))/100.0 as Net_Revenue,
 status
FROM orders
WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01'
GROUP BY status
;

Two SUM expressions, one plain and one filtered, run in a single scan over the October rows, returning gross and net side by side for each status.

Next step

Adding an explicit difference column, computed as Gross minus Net_Revenue, would let Nura see the excluded revenue value per status directly in the result without manual subtraction.

Problem

Meridian Retail (simulated retailer) operations lead Chidi Okafor was receiving three different customer counts from different teams (2,331, 2,131, and 2,200) ahead of an investor meeting and needed to know which was correct and why the others differed. The task was to reproduce all three counting methods with explicit SQL.

What I did

Three separate, minimal queries each isolate one counting logic so the difference between them is transparent. The first query runs COUNT(*) with no filters, capturing every row including duplicates, which explains the marketing figure. Filtering on is_duplicate = FALSE in the second query applies the deduplication logic that operations relies on. Adding total_orders > 0 in the third query narrows further to customers who have placed at least one order, which is the finance definition of an active customer.

Outcome

Chidi could see in code exactly why each team's number differed, though the submission did not yet include a written recommendation on which figure to use for the investor conversation.

SELECT COUNT(*)
FROM customers
;

SELECT COUNT(*) 
FROM customers 
WHERE is_duplicate = FALSE
;

SELECT COUNT(*) 
FROM customers 
WHERE is_duplicate = FALSE AND total_orders > 0

Three queries apply progressively stricter filters, raw count, then duplicate-excluded, then active-only, so the source of each team's figure is traceable to a single condition.

Next step

The next step I would add is a written explanation attached to the query output stating which count Chidi should cite and what assumption each of the other two encodes, since the reviewer noted that part was missing.