Tijani Ahmad

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to resolve a conflict where marketing, operations, and finance each reported a different customer total from the same underlying export. The task was to reproduce all three counts using live Excel formulas and explain why each number differed.

What I did

COUNTA on the customer ID column captured every row regardless of duplicate status, matching the way marketing counts signups. To replicate the operations view, COUNTIF filtered to rows where the is_duplicate column stored a boolean FALSE, which required testing against the actual stored data type rather than a text string. COUNTIFS then added a second condition requiring total_orders greater than zero, producing the finance definition of an active, non-duplicate customer. All three formulas were placed on a dedicated sheet named CUSTOMER COUNT VALIDATION with column headers for method, count, and explanation.

Outcome

Chidi received a single summary table he could show stakeholders, with the count difference explained by methodology rather than data error.

Next step

It would be worth identifying which specific rows account for the 26 customers operations counts but finance excludes, to confirm they are genuine zero-order accounts rather than a data entry issue.

Problem

Nura Ibrahim at Meridian Retail needed a single-query baseline of the orders table before any filters were applied. The required outputs were total order count, the earliest and latest order dates, and total revenue converted from cents to naira.

What I did

COUNT(*) gave the total row count across all orders. MIN and MAX on the created_at column captured the full date span without requiring a separate date table. Dividing SUM(total_cents) by 100.0 rather than the integer 100 preserved decimal precision in the naira output, which matters when the underlying figures run into the billions.

Outcome

Nura had a confirmed, single-source baseline to compare against any filtered or aggregated figures produced later in the investigation.

SELECT 
 COUNT(*) AS total_orders,
 MIN(created_at) AS earliest_order,
 MAX(created_at) AS latest_order,
 SUM(total_cents)/100.0 AS total_revenue_naira
FROM retail.orders;

This query retrieves four summary metrics in one scan of retail.orders, with the division by 100.0 converting stored cent values to naira with decimal precision intact.

Next step

Breaking the same summary down by year would show whether order volume and revenue grew steadily across the four-year span or concentrated in certain periods.

Problem

Nura Ibrahim at Meridian Retail needed to understand the origin of a reported revenue gap in October 2023. The request was to show revenue by order status for that month, with both a gross total across all statuses and a net total that excluded cancelled, returned, and refunded orders.

What I did

Filtering with created_at >= '2023-10-01' AND created_at < '2023-11-01' cleanly isolates October without any time-of-day edge cases on the boundary date. A CASE WHEN expression inside SUM assigned each row's total_cents to the net column only when its status was not in the three excluded categories, and zero otherwise. Grouping by status then let both gross and net columns sit side by side on the same row for each status, making the gap immediately readable. Dividing both SUM expressions by 100.0 kept the naira output precise.

Outcome

Nura could trace the exact naira amount of the discrepancy to specific status categories rather than treating the gap as an unexplained variance.

SELECT status,
 SUM(total_cents)/100.0 AS gross,
 SUM(CASE WHEN status NOT IN ('cancelled','returned','refunded')
 THEN total_cents ELSE 0 END)/100.0 AS net
FROM retail.orders
WHERE created_at >= '2023-10-01'
 AND created_at < '2023-11-01'
GROUP BY status;

The CASE WHEN inside SUM produces a net revenue column by zeroing out the three non-revenue statuses while the outer GROUP BY keeps each status on its own row for comparison.

Next step

A useful extension would be to run the same gross-versus-net comparison across all months in 2023 to see whether October's gap ratio was typical or an outlier.

Problem

Nura Ibrahim at Meridian Retail needed to reconcile two conflicting October 2023 revenue figures before a board presentation: the finance system showed approximately NGN 38.5 million while the sales dashboard showed approximately NGN 56.9 million. The task was to produce a status-level breakdown that explained both numbers from the same source data.

What I did

The same date boundary pattern, created_at >= '2023-10-01' AND created_at < '2023-11-01', isolated the correct month without risk of including partial days at the boundary. CASE WHEN inside SUM replicated the finance system's logic by excluding cancelled, returned, and refunded statuses from the net column. Dividing by 100.0 preserved kobo-level precision in both columns. Grouping by status gave the finance team a row for each category so they could audit exactly which statuses contributed to each total.

Outcome

Nura received a single query result that validated both figures and showed they were drawn from the same data with different inclusion rules, giving her a clear explanation to bring to the board.

SELECT status,
 SUM(total_cents)/100.0 AS gross,
 SUM(CASE WHEN status NOT IN ('cancelled','returned','refunded')
 THEN total_cents ELSE 0 END)/100.0 AS net
FROM retail.orders
WHERE created_at >= '2023-10-01'
 AND created_at < '2023-11-01'
GROUP BY status;

Placing both gross and net SUM expressions in the same SELECT means the reconciliation lives in a single result set, making the methodology difference between the two reporting systems directly visible.

Next step

Checking whether the sales dashboard applies the same date boundary logic would confirm whether the NGN 56.9 million match is exact or coincidental due to rounding.

Problem

Nura Ibrahim, CFO at Meridian Retail (simulated retailer), flagged a revenue discrepancy and needed a clear picture of what order statuses existed in the orders table before any filtering began. The specific question was how many orders carried each status and whether the full set of statuses was accounted for.

What I did

Starting with a GROUP BY on the status column paired with COUNT(*) gave me a clean frequency count for every distinct status in one pass. Qualifying the table as retail.orders made the reference unambiguous regardless of the session search path. Ordering the results by order_count DESC meant the most significant category surfaced at the top without any manual sorting.

Outcome

Nura received a complete, ranked inventory of order statuses that she could use as a confirmed baseline before any further filtering or revenue reconciliation.

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

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

Next step

A useful follow-up would be to break the status counts down by month to see whether the share of cancelled or returned orders shifted over time.