Emmanuel Ohiomoba

Problem

The CFO at Meridian Retail (simulated retailer) flagged a conflict between two systems: the Finance platform showed NGN 38.5M for October 2023 while the sales dashboard showed NGN 56.9M. Before a board presentation, I was asked to pull October orders broken down by status with both gross and net revenue figures so the two numbers could be reconciled.

What I did

To avoid edge cases with time components, I used a half-open interval date filter rather than a between clause: created_at >= 2023-10-01 AND created_at < 2023-11-01. Gross revenue was computed by summing total_cents for all statuses and dividing by 100 to convert to naira. Net revenue required a CASE inside SUM that zeroes out rows where status is cancelled, returned, or refunded, which kept both measures in a single pass over the data. Sorting by gross_revenue_naira DESC made the seven status rows immediately readable for a board-prep audience.

Outcome

The CFO received a single query result that resolved the discrepancy and provided the breakdown needed to present confidently to the board.

SELECT 
 status,
 COUNT(*) AS order_count,
 SUM(total_cents) / 100.0 AS gross_revenue_naira,
 SUM(CASE 
 WHEN status NOT IN ('cancelled', 'returned', 'refunded') 
 THEN total_cents 
 ELSE 0 
 END) / 100.0 AS net_revenue_naira
FROM orders
WHERE created_at >= '2023-10-01' 
 AND created_at < '2023-11-01'
GROUP BY status
ORDER BY gross_revenue_naira DESC;

The CASE inside SUM computes gross and net revenue in a single scan, while the half-open date interval ensures no orders are missed or double-counted due to time components in the timestamp.

Next step

A useful follow-on would be running the same gross-vs-net split for each prior month to show whether the gap between the two figures is growing, shrinking, or stable over time.

Problem

Before any analysis could begin on Meridian Retail (simulated retailer) data, I was asked to open Meridian_Data.xlsx and document what was in it: sheet structure, column layout, order statuses, and any data quality issues visible from a first inspection. The minimum bar was three issues flagged in a new Data Audit sheet.

What I did

Working sheet by sheet, I recorded row and column counts for all six sheets, including 1,849 orders, 4,030 line items, and 2,331 customers. For the Orders sheet specifically, I listed all 16 column names in order and tallied all seven status values with counts and percentages. Four issues were documented: the line_total column reflects quantity times charged price rather than accounting for the separate discount column, with the discount column summing to NGN 2,064,064.59; 66 rows carry negative stock_qty values; 200 records are flagged is_duplicate but the matches appear to be name-based rather than exact email or phone matches, creating a discrepancy between the raw customer count of 2,331 and the deduplicated count of 2,131; and the legacy_order_ref column is 100 percent null across all rows. Documenting what did not break was included as a final section to make the audit credible rather than one-sided.

Outcome

The audit sheet gave any analyst picking up the file a clear map of its structure and a prioritised list of issues to resolve or account for before building calculations on top of the data.

Next step

Investigating the 66 negative stock_qty rows further to determine whether they reflect genuine oversell events or data entry errors would clarify whether they should be corrected or treated as a separate category in inventory reporting.

Problem

The merchandising lead at Pitchside Soccer Co. (simulated sports merchandise company) needed to identify group-stage goal-scorers who entered the 2026 World Cup with low pre-tournament star power and had since broken out. The specific question was which players should marketing chase for new SKUs before competitors act, and whether the internal breakout upgrade mechanic had already fired for each of them.

What I did

Four tables were joined: match_events to matches to players to teams, with the WHERE clause scoping events to group-stage matches and to the goal and penalty_goal event types only. Goals were counted with COUNT(*) per player. Two derived columns handled the business flags: a CASE expression marks is_emerging as TRUE when a player's pre-tournament star_power is below 30 and their goal count reaches two or more, and an EXISTS correlated subquery checks the player_status_history table for a breakout_upgrade trigger_reason row rather than using a LEFT JOIN, which kept the logic self-contained and readable. Ordering by goals_scored DESC then star_power ASC ensures the lowest-profile high-scorers surface at the top of the 20-row result.

Outcome

Marketing received a ranked, flagged list of the top 20 group-stage scorers with clear signals distinguishing known stars from genuine surprises, enabling them to prioritise new SKU development before the knockout rounds.

SELECT
 p.display_name AS player_name,
 t.name AS team_name,
 p.position,
 p.star_power,
 p.effective_star_power,
 COUNT(*) AS goals_scored,
 CASE 
 WHEN p.star_power < 30 AND COUNT(*) >= 2 THEN TRUE 
 ELSE FALSE 
 END AS is_emerging,
 EXISTS (
 SELECT 1 
 FROM player_status_history psh
 WHERE psh.player_id = p.player_id
 AND psh.trigger_reason = 'breakout_upgrade'
 ) AS has_breakout_upgrade

The EXISTS subquery checks the history table per player without adding rows to the result set, while the CASE flag combines a pre-tournament attribute with a post-tournament aggregate in a single readable expression.

Next step

Comparing the is_emerging players' team jersey sales volume before and after the group stage would confirm whether the breakout in goals actually translated into a measurable demand spike worth prioritising for production.

Problem

Before any reconciliation work could begin, the CFO at Meridian Retail (simulated retailer) needed a clear picture of what order statuses existed in the orders table and how volume and revenue were distributed across them. The question was straightforward: what statuses are present and how many orders does each carry?

What I did

The core query groups by status and counts rows, but I extended it to include SUM(total_cents) / 100.0 as a revenue column alongside the count. Converting from cents to naira in the same step meant the output was immediately usable without a separate calculation. Ordering by total_revenue DESC surfaces the highest-value statuses first, which made it immediately visible that cancelled, returned, and refunded rows together represent a material share of gross revenue that must be stripped out for net figures.

Outcome

The status map gave the CFO and the investigation a confirmed list of exclusion categories and their revenue weight before any filtering logic was written.

SELECT 
 status,
 COUNT(*) AS order_count,
 SUM(total_cents) / 100.0 AS total_revenue
FROM orders
GROUP BY status
ORDER BY total_revenue DESC;

Adding total_revenue to what was asked as a counts-only query surfaced the revenue weight of each status in one pass, which directly informed which statuses needed to be excluded in later reconciliation work.

Next step

Breaking the delivered and shipped rows down by month would show whether volume and revenue composition are stable across the year or shifting in a way that affects the reconciliation picture.

Problem

The CFO at Meridian Retail (simulated retailer) needed October 2023 revenue verified in Excel as a cross-check against the Finance system. The ask was explicit: show gross revenue across all statuses, net revenue excluding cancelled, returned, and refunded orders, the gap between the two, and all figures formatted as Nigerian naira with thousand separators.

What I did

Every figure was built with live SUMIFS formulas referencing the order_date column on the Orders sheet, using DATE(2023,10,1) and DATE(2023,10,31) as bounds so the date logic is transparent and auditable. Rather than excluding the three status categories in a single combined formula, I broke them out into separate SUMIFS rows for cancelled, returned, and refunded individually before summing to net revenue. This gave the CFO a richer reconciliation view. A custom format of NGN #,##0 with thousand separators was applied to all result cells, and a notes section documenting the methodology was added below the figures.

Outcome

The CFO received a formatted, self-documenting Excel sheet that confirmed the Finance system figure, explained the dashboard figure, and showed exactly which status buckets account for the difference.

Next step

Extending the model to cover all twelve months of 2023 would show whether the cancelled-returned-refunded gap as a share of gross revenue is growing across the year, which would be a useful input for the Finance team's planning assumptions.