Abiodun Olaniyi

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was heading into an investor meeting with three different customer counts circulating across teams: 2,331, 2,131, and 2,200. The question was which number was correct, why the others were wrong, and how to make that answer readable without extra explanation.

What I did

Using UNION ALL, I produced all three counts in a single query so the comparison sits in one result set rather than across separate queries. Each row carries a self-documenting counting_method label that names the logic and the resulting number inline, so the output explains itself. The WHERE clause conditions step from raw rows, to is_duplicate = FALSE, to is_duplicate = FALSE AND total_orders > 0, mirroring the three methodologies teams were using. Sorting by customer_count DESC puts the broadest count at the top and narrows down to the most restrictive, which makes the hierarchy intuitive to scan.

Outcome

Chidi received a single query output he could share in the room, with each count labelled by its logic, so the disagreement between teams could be explained rather than just overridden.

SELECT 'COUNT(*) = 2331 - Marketing raw (includes duplicates)' AS counting_method,
 COUNT(*) AS customer_count
FROM retail.customers

UNION ALL

SELECT 'COUNT(*) WHERE is_duplicate = FALSE = 2131 - DEFENSIBLE for investor (deduped)',
 COUNT(*)
FROM retail.customers
WHERE is_duplicate = FALSE

UNION ALL

SELECT 'COUNT(*) WHERE is_duplicate = FALSE AND total_orders > 0 = 2105 - Finance active non-duplicate (>=1 order)',
 COUNT(*)
FROM retail.customers
WHERE is_duplicate = FALSE
 AND total_orders > 0

ORDER BY customer_count DESC;

UNION ALL stacks all three counting methods into one ranked result set, and the inline labels mean a non-technical reader can interpret each row without a separate explanation document.

Next step

A useful follow-on would be breaking the 200 duplicate records down by how they were created, whether by channel, registration date, or email format, to understand whether deduplication needs to run as a recurring job or was a one-time data entry issue.

Problem

Nura Ibrahim, CFO at Meridian Retail (simulated retailer), flagged a revenue discrepancy and needed a starting point before any filtering began. The specific ask was to identify every order status in the table and how many orders carried each one, sorted from most to least common.

What I did

The query groups all rows in the orders table by status and counts each group, then sorts the result descending so the dominant status appears first. Keeping the query to three clauses, GROUP BY, COUNT, and ORDER BY, made it easy to verify correctness and extend later. Omitting the schema prefix worked correctly given the environment's search path, which the reviewer noted as appropriate context awareness.

Outcome

Nura had a confirmed map of order statuses and their volumes before any revenue filter was applied, giving the investigation a documented starting point rather than an assumption.

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

GROUP BY status collapses one row per distinct status value and COUNT(*) tallies each group, producing a frequency profile of the categorical column in three lines.

Next step

The natural next cut would be filtering revenue impact by status, summing total_cents for cancelled, returned, and refunded orders separately, to quantify how much of the gross revenue figure those statuses account for.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed defensible per-hub performance metrics for an internal review. The ask covered three things: a delivery rate breakdown per hub, a complaint rate normalised per 1,000 shipments so large hubs were not penalised by volume alone, and a plain-language conclusion about which hub warranted the most concern.

What I did

Part one pulled from the existing logistics.v_hub_performance view, which already summarises shipments and delivery rates at hub grain. For the complaint rate, I joined from that view through shipments to complaints using LEFT JOIN at both steps, so hubs with zero complaints remain in the output rather than being dropped. The formula COUNT(c.complaint_id) * 1000.0 / NULLIF(hp.total_shipments, 0) handles two specific traps: multiplying by 1000.0 instead of 1000 forces floating-point division, and NULLIF prevents a division-by-zero error on any hub with no recorded shipments. Sorting by delivery_rate_pct ascending puts the worst-performing hubs first, with complaint rate as a tiebreaker.

Outcome

Funmi received a ranked hub table she could present to the board with numeric backing, along with a clear note that if no single hub dominates both metrics, the data supports saying the problem is systemic rather than isolated.

SELECT
 hp.hub_id,
 hp.name,
 hp.city,
 hp.total_shipments,
 hp.delivered,
 hp.failed,
 hp.delivery_rate_pct,
 COUNT(c.complaint_id) AS complaints,
 ROUND(COUNT(c.complaint_id) * 1000.0 / NULLIF(hp.total_shipments, 0), 2) AS complaints_per_1000
FROM logistics.v_hub_performance hp
LEFT JOIN logistics.shipments s ON s.destination_hub_id = hp.hub_id
LEFT JOIN logistics.complaints c ON c.shipment_id = s.shipment_id
GROUP BY hp.hub_id, hp.name, hp.city, hp.total_shipments, hp.delivered, hp.failed, hp.delivery_rate_pct
ORDER BY hp.delivery_rate_pct ASC, COUNT(c.complaint_id) * 1000.0 / NULLIF(hp.total_shipments, 0) DESC;

The chained LEFT JOINs from the hub view through shipments to complaints preserve all hubs in the output, and NULLIF inside the rate formula prevents a silent division-by-zero on any hub with no shipment volume recorded.

Next step

A useful extension would be breaking complaints_per_1000 down by complaint category per hub, to see whether a high-complaint hub is concentrated in one failure type like damage or delay, or spread across all categories.

Problem

Funmi Alade at FlowRoute Logistics (simulated logistics company) needed to frame the scale of the complaint problem for a board pack before deeper investigation began. The requirement was a total complaint count, a per-category breakdown with percentage share, sorted most to least common, and a plain recommendation on which category to lead with.

What I did

Grouping logistics.complaints by category and counting rows per group gives the frequency breakdown. Percentage of total is calculated inline using a correlated subquery, COUNT(*) divided by a SELECT COUNT(*) FROM the same table, with 100.0 as the multiplier to force floating-point arithmetic and avoid integer truncation. Wrapping the result in ROUND keeps the output clean for a board-facing document. Sorting by complaint_count descending surfaces the category Funmi should lead with at the top of the result set, without requiring a separate query or manual reordering.

Outcome

Funmi had a single query output ready for the board pack, with complaint volume and relative weight per category laid out in the order she needed to present them.

SELECT
 category,
 COUNT(*) AS complaint_count,
 ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM logistics.complaints), 2) AS percentage_of_total
FROM logistics.complaints
GROUP BY category
ORDER BY complaint_count DESC;

The correlated subquery in the SELECT clause divides each category count by the table-wide total, and multiplying by 100.0 rather than 100 ensures the division produces a decimal rather than being truncated to zero.

Next step

Breaking the top complaint category down by hub or by shipment date range would show whether the volume is concentrated in a specific location or period, which would change how the board frames the remediation ask.

Problem

As part of Nura Ibrahim's ongoing revenue discrepancy investigation at Meridian Retail (simulated retailer), the next step was a clean baseline summary of the orders table before any filters were applied. The requirement was total order count, the earliest and latest order dates, and gross revenue converted from cents to naira.

What I did

COUNT(*) gives the row total without excluding any status. MIN and MAX on created_at with a ::date cast strip the time component, producing clean calendar dates rather than timestamps. Total revenue requires summing the total_cents column and dividing by 100.0, where using 100.0 rather than 100 prevents the division from truncating to an integer before ROUND is applied. All four metrics fit in a single SELECT against retail.orders, giving Nura one verifiable number set to anchor every subsequent filtered query against.

Outcome

Nura received a confirmed baseline with exact figures, so any filtered subset of orders could be immediately compared against a verified total rather than an assumed one.

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;

Dividing total_cents by 100.0 before applying ROUND converts the stored integer cents value to naira with decimal precision intact, and ::date on the timestamp columns removes the time portion for a readable date range.

Next step

The logical follow-on is filtering total_cents by status to separate revenue that completed from revenue that was cancelled, returned, or refunded, to quantify the net versus gross gap that triggered the discrepancy flag.