Suleiman Aliyu

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was receiving complaints from store managers about empty shelves. Inventory records were showing negative stock quantities, meaning items were being sold beyond available supply. The task was to identify all affected products with store details and rank stores by total financial exposure.

What I did

Joining `inventory` to `stores` and `products` gives the right grain: one row per product-store combination in negative stock. A `WHERE i.stock_qty < 0` filter isolates the problem records. The exposure formula `ABS(i.stock_qty) * (p.list_price_cents / 100.0)` converts the unit shortfall into a naira value using floating-point division, with `.0` preventing integer truncation. Aggregating at store level with `COUNT(DISTINCT p.product_id)`, total units oversold, and summed exposure, then ordering by exposure descending, gives Chidi a prioritised action list rather than a flat record dump.

Outcome

Chidi received a ranked store table showing exactly where to direct replenishment effort first, with the financial scale of each location's shortfall visible at a glance.

SELECT 
 s.store_id,
 s.name,
 s.city,
 COUNT(DISTINCT p.product_id) AS products_in_negative_stock,
 SUM(ABS(i.stock_qty)) AS total_units_oversold,
 SUM(ABS(i.stock_qty) * (p.list_price_cents / 100.0)) AS total_backorder_exposure
FROM inventory i
JOIN stores s ON i.store_id = s.store_id
JOIN products p ON i.product_id = p.product_id
WHERE i.stock_qty < 0
GROUP BY s.store_id, s.name, s.city
ORDER BY total_backorder_exposure DESC;

The exposure calculation multiplies the absolute unit shortfall by the per-unit naira price, converting a stock count into a financial risk figure that operations management can act on directly.

Next step

Filtering further by product category would show whether the negative stock is concentrated in a particular line, which would point to a supplier or ordering process issue rather than a store-level one.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed October 2023 revenue calculated in Excel to cross-check against the Finance system. The specific question was how much of gross revenue was lost to cancelled, returned, and refunded orders.

What I did

Using SUMIFS, I filtered the `total_naira` column by an October 2023 date range to produce the gross figure. The net formula chains three additional `<>` criteria to exclude the three non-revenue statuses in a single expression. The gap in the third cell is a live subtraction formula rather than a hardcoded number, so it updates if the source data changes. All three values are formatted with the NGN prefix and thousand separators to match the Finance team's reporting style.

Outcome

Nura received a live, auditable Excel model that confirmed which figure each system was reporting and quantified the difference for her board presentation.

Next step

Splitting the NGN 18,426,410.13 gap by individual status (cancelled vs returned vs refunded) would show which category drives the most revenue loss and where intervention is most valuable.

Problem

Sarah Chen at Nexus Financial (simulated bank) needed a clean regional summary of all 45 branches for a board geographic profile. The ask was branch count, average square footage, and full-service branch count per region, ordered by branch count descending.

What I did

Querying `nexus_banking.branches`, I grouped by region and used `COUNT(*) FILTER (WHERE is_full_service = TRUE)` to produce the conditional count without a subquery or CASE expression. `ROUND(AVG(square_feet), 0)` keeps square footage as whole numbers, appropriate for a board-level table. Ordering by `branch_count DESC` surfaces the West region at the top and makes the regional ranking immediately readable.

Outcome

The query gave Sarah a single, correctly ordered table ready to drop into the board geographic profile without further formatting.

SELECT 
region,
COUNT(*) as branch_count,
ROUND(AVG(square_feet), 0) AS avg_sqft,
COUNT(*) FILTER (where is_full_service = TRUE) AS full_service_count
FROM nexus_banking.branches
GROUP BY region
ORDER BY branch_count DESC;

The FILTER clause handles the conditional count in a single pass over the table, avoiding the need for a subquery or CASE WHEN pivot.

Next step

A useful next step would be breaking down full-service share within each region to see whether any region is under-served relative to its branch count.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a customer growth visual as the centrepiece of an investor presentation slide. The chart had to show monthly new customer counts alongside a running cumulative total, using Meridian brand colours.

What I did

A PivotTable on a new `Customer Growth` sheet sources from the full Customers range and filters `is_duplicate` to FALSE, so only unique customer acquisitions are counted. Monthly grouping spans 2021 through 2024, giving 48 data points. Column C uses the expanding-range pattern `=SUM($B$5:B5)` dragged down to accumulate the running total at each month. The combination chart pairs bars for monthly new customers with a line for the cumulative total, styled with orange bars and a navy line to match Meridian branding.

Outcome

The deliverable was a self-contained chart and PivotTable ready to embed in the investor presentation, with brand colours already applied.

Next step

Breaking acquisition down by channel alongside the monthly trend would let Chidi show investors which acquisition source is driving growth, not just that growth is occurring.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) faced a discrepancy between the sales dashboard showing NGN 56.9M and the Finance system showing NGN 38.5M for October 2023. The task was to pull October orders broken down by status with both gross and net revenue so the two figures could be reconciled before a board presentation.

What I did

Filtering with a half-open interval (`created_at >= '2023-10-01' AND created_at < '2023-11-01'`) avoids any end-of-day boundary ambiguity. Within the SELECT, `SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled', 'returned', 'refunded'))` isolates net revenue in the same pass that calculates gross, and a direct subtraction produces the `revenue_gap_naira` column. `TO_CHAR` with the `FM` mask and the `NGN ` prefix formats every currency figure for a non-technical reader without requiring post-processing in a spreadsheet.

Outcome

Nura could present a single query result to the board that showed both figures, their source logic, and the NGN 18,426,410.13 gap in one readable table.

SELECT 
status,
COUNT(*) AS total_orders,
'NGN ' || TO_CHAR(SUM(total_cents) / 100.0, 'FM99,999,999.00') AS gross_revenue_naira,
'NGN ' || TO_CHAR(COALESCE(SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled', 'returned', 'refunded')), 0) / 100.0, 'FM99,999,999.00') AS net_revenue_naira,
'NGN ' || TO_CHAR((SUM(total_cents) - COALESCE(SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled', 'returned', 'refunded')), 0)) / 100.0, 'FM99,999,999.00') AS revenue_gap_naira
FROM retail.orders
WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01'
GROUP BY status
ORDER BY sum(total_cents) DESC;

The FILTER clause inside the aggregate computes net revenue inline, so both gross and net figures come from a single table scan grouped by status.

Next step

Running the same status breakdown across all months of 2023 would show whether October's cancellation and return volumes are typical or anomalous.