Enoch Adefemi

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim flagged a revenue discrepancy and needed a baseline picture of the orders table before filtering could begin. The specific question was: what distinct order statuses exist, and how many orders carry each one?

What I did

A single SELECT with COUNT(*) and GROUP BY status against retail.orders produced the full status breakdown in one pass. Ordering by the count descending was a deliberate choice so the dominant status would appear at the top without any additional sorting step. The explicit schema prefix retail.orders was kept in place even though the search path was already set, to keep the query unambiguous in any session context.

Outcome

The output gave Nura a ranked, complete status map to guide the next filtering step in her investigation, with no manual counting or pivoting required.

Next step

A useful follow-on would be to calculate the naira value associated with each status, so the revenue impact of cancelled, returned, and refunded orders can be compared directly rather than just by order count.

Problem

Meridian Retail CFO Nura Ibrahim needed an October 2023 revenue figure in Excel to cross-check against the Finance system. The request specified both a gross figure covering all statuses and a net figure with cancelled, returned, and refunded orders excluded, along with the gap between the two.

What I did

Two SUMIFS formulas were built against the Orders sheet, bounding the date range with EOMONTH to capture the full month reliably regardless of the exact day count. The net revenue formula applied three separate criteria pairs, each using a not-equal operator to exclude one of the three disqualifying statuses in a single expression. The gap in B4 was written as =B2-B3 so it stays live if either source figure is recalculated. All three cells were formatted with the NGN Naira code and thousand separators to meet the display requirement exactly.

Outcome

Nura received a single-sheet view she could compare directly against the Finance system, with live formulas that would update if the underlying data changed and Naira formatting already in place for reporting.

Next step

Breaking the NGN 18,426,410.13 gap further by status would show whether cancellations, returns, or refunds drive the larger share, which would point to different operational fixes.

Problem

FlowRoute Logistics (simulated logistics company) Head of Customer Experience Funmi Alade needed a short, phone-readable email before a Friday meeting. She asked for the top complaint category with its share of total, an honest read on whether one hub stood out as worst, and a single memorable number she could quote to a colleague.

What I did

The response led with the top category and its percentage, which directly answered the first item. For the hub question, the data showed no clear outlier, so rather than naming one arbitrarily, I reported the range of 35 to 37 complaints per 1,000 shipments to show the spread was narrow. The email was kept under 150 words to match the stated reading context. The third item, the single memorable number Funmi requested, was not included, which the reviewer identified as a gap in the submission.

Outcome

Funmi received a direct, honest picture of the top category and hub spread before her meeting, though the submission did not provide the one memorable standalone number she had specified.

Next step

Identifying one concrete operational metric, such as the absolute complaint count for the single worst month across all hubs, would have given Funmi the quotable number the submission missed.

Problem

FlowRoute Logistics Head of Customer Experience Funmi Alade needed three things for an upcoming board pack: total complaint volume, a ranked breakdown by category with percentages, and a one-sentence recommendation on which category to lead with. The source was the logistics.complaints table with one row per complaint.

What I did

Rather than using a subquery or CTE to get the grand total separately, I nested COUNT(*) inside SUM(...) OVER () to produce the denominator inline, which keeps the query to a single pass. Dividing the per-category count by that window total and multiplying by 100.0 avoids integer truncation, and ROUND(..., 1) trims the result to one decimal for board-pack readability. Carrying TOTAL_COMPLAINTS as a column in every output row means the reader can verify the denominator without a separate query. A comment at the end of the script delivered the one-sentence board recommendation in plain language.

Outcome

Funmi received a ranked, percentage-labelled table with the grand total visible in every row, plus a written recommendation she could quote directly to the board.

SELECT
 SUM(COUNT(*))OVER() AS TOTAL_COMPLAINTS,
 category,
 COUNT(*) AS CategoryComplaint,
 ROUND(COUNT(*) * 100.0/SUM(COUNT(*)) OVER (), 1) AS ComplaintPercentage
FROM complaints
GROUP BY category
ORDER BY CategoryComplaint DESC

-- Lead with 'Lost' parcels -- at 25.6% of all complaints, it is the single largest driver of customer dissatisfaction.

SUM(COUNT(*)) OVER () computes the grand total by summing the per-group counts produced by GROUP BY in one pass, avoiding a subquery and making the percentage denominator visible as a column in every output row.

Next step

Filtering complaints by date to see whether the Lost category share has grown, stayed flat, or narrowed over recent months would tell Funmi whether this is a worsening trend or a stable baseline.

Problem

Meridian Retail CFO Nura Ibrahim needed a single-row baseline summary of the orders table before any filtering began. The request covered four metrics: total order count, earliest order date, latest order date, and total revenue in naira.

What I did

COUNT(*) returns the full row count without any filter condition, which is the correct baseline measure. Wrapping MIN and MAX of the created_at timestamp column in ::date casts strips the time component, producing clean calendar dates rather than datetime strings. The revenue calculation divides SUM(total_cents) by 100.0 rather than 100 to force floating-point division and preserve the kobo component, with ROUND(..., 2) applied to hold the result to two decimal places. Descriptive aliasing, using Total_Revenue_In_Naira instead of a shorter abbreviation, was a deliberate choice to make the output self-explanatory without a data dictionary.

Outcome

Nura received a clean single-row baseline she could use as the denominator for every subsequent filtered query 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_In_Naira
FROM retail.orders

The ::date cast on MIN and MAX removes the timestamp portion for clean date output, and dividing by 100.0 instead of 100 ensures the division returns a decimal result before ROUND is applied.

Next step

Repeating the same four metrics filtered to only revenue-impacting statuses, cancelled, returned, and refunded, would immediately show what share of the NGN 2,305,853,142.45 total is at risk.