Mayowa Ajoye Daniel

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, showing both gross and net revenue and making the gap between the two figures explicit.

What I did

To scope the data safely to October, I used a half-open interval on the created_at column, avoiding any boundary ambiguity from time components. Within a single SELECT, I computed gross revenue as a plain SUM and net revenue using a FILTER clause that excluded cancelled, returned, and refunded orders, so both figures came from one table scan. A UNION ALL block appended a TOTAL row beneath the per-status breakdown, giving the board exactly two reconciliation anchors alongside the full detail. The difference_naira column was derived inline, making which statuses drove the gap readable without any supporting narrative.

Outcome

The CFO received a single query result that confirmed both system figures were correct and showed the board exactly which order statuses produced the difference.

SELECT 
 status::text AS status,
 COUNT(*) AS order_count,
 SUM(total_cents) / 100.0 AS gross_naira,
 SUM(total_cents) FILTER (
 WHERE status NOT IN ('cancelled', 'returned', 'refunded') 
 ) / 100.0 AS net_naira,
 (
 SUM(total_cents)
 -SUM(total_cents) FILTER (WHERE status NOT IN 
 ('cancelled', 'returned', 'refunded')
 )
 ) / 100.0 AS difference_naira
FROM orders 
WHERE created_at >= '2023-10-01'
AND created_at < '2023-11-01'
GROUP BY status

This block computes gross naira, net naira, and their difference in one pass using the FILTER clause, removing the need for a subquery or CTE while keeping the per-status logic transparent.

Next step

A useful follow-on would be breaking down the cancelled and returned orders by product category to see whether the revenue gap is concentrated in a particular line of stock.

Problem

Before any analysis of Meridian Retail (simulated retailer) could begin, I was asked to open the source Excel file, document what it contained, and flag at least three genuine data quality problems in a dedicated audit sheet.

What I did

I created a Data Audit sheet and recorded the file structure, column inventory for the Orders sheet, and the distinct order statuses found. A PivotTable sourced from the full Orders range surfaced all seven statuses with revenue breakdowns. Beyond the minimum requirement, GETPIVOTDATA formulas in the audit sheet calculated a net revenue figure for the three non-problematic statuses and quantified the gap against gross. The three confirmed issues documented were negative stock quantities, inconsistent category naming across sheets, and flagged duplicate customer records.

Outcome

The audit sheet gave any analyst picking up the file a clear pre-flight checklist and an early gross revenue figure of NGN 56,876,792.48 to use as a reconciliation anchor.

Next step

Quantifying how many rows each quality issue affects, specifically the count of negative stock records and the count of duplicate customers, would make the audit actionable for a data engineering fix.

Problem

A stakeholder at Meridian Retail (simulated retailer) was receiving three different customer counts from three internal teams, 2,331, 2,200, and 2,131, and needed one defensible number before meeting with an investor. The task was to show how each count arose and identify the correct figure.

What I did

Rather than running three separate queries, I used a single SELECT with three COUNT expressions, each scoped by a different FILTER condition, so the relationship between the figures was visible in one result set. The first column counted all rows, capturing the marketing team's raw total. Filtering on is_duplicate = FALSE produced the operations team's deduplicated figure, and adding total_orders > 0 to that filter reproduced the finance team's active-customer view. Structuring it this way meant the query itself was the explanation, not a separate written note.

Outcome

The stakeholder received a single, auditable query result that explained all three competing numbers and identified 2,131 as the figure that could be defended in an investor conversation.

SELECT 
 COUNT(*) AS total_rows,
 COUNT(*)FILTER (WHERE is_duplicate = FALSE) AS unique_customers,
 COUNT(*)FILTER (WHERE is_duplicate = false AND total_orders > 0 )AS active_unique_customers
FROM customers;

Three COUNT expressions with progressively stricter FILTER conditions produce all three team counts in one row, making the logic of each figure immediately comparable.

Next step

Checking whether the 200 duplicate records share email addresses or phone numbers would confirm whether they are true duplicates or a data entry pattern that needs a different deduplication rule.

Problem

The CFO at Meridian Retail (simulated retailer) needed to understand where an NGN 18.4M revenue gap was coming from. I was asked to produce a status-level breakdown showing gross revenue, net revenue excluding problematic statuses, and the difference for each row, all scoped to October 2023.

What I did

For the net revenue column, I used a CASE expression inside SUM rather than a FILTER clause, producing the same conditional aggregate in a slightly different but equally readable pattern. The difference_naira column was computed as a derived expression within the same SELECT, so no second query or manual subtraction was needed. A UNION ALL block added a summary row covering all October orders, giving the CFO both a per-status view and a bottom-line total in one result. Date scoping used the half-open interval pattern to handle any time-of-day values on the boundary date.

Outcome

The CFO had a self-contained query result that showed the source of the gap without requiring any mental arithmetic or a follow-up question.

SUM(CASE 
 WHEN status NOT IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END) / 100.0 AS net_naira,
 (SUM(total_cents) - SUM(CASE 
 WHEN status NOT IN ('cancelled','returned', 'refunded')
 THEN total_cents
 ELSE 0
 END)) / 100.0 AS difference_naira

The CASE expression inside SUM conditionally zeros out problematic-status values, and the difference is derived in the same SELECT, keeping both net revenue and the gap calculation self-contained.

Next step

Comparing the proportion of cancelled and returned orders month-over-month would show whether October's gap is typical or an outlier worth investigating further.

Problem

The CFO at Meridian Retail (simulated retailer) flagged a revenue discrepancy and needed a preliminary picture of the orders table before deeper analysis could begin. The task was to identify every distinct order status present in October 2023 and show how many orders and how much revenue each status held.

What I did

A GROUP BY on the status column with COUNT and SUM gave the full status inventory in one query. Converting total_cents to naira by dividing by 100.0 kept the output in a unit the CFO could read directly. Scoping to October with a half-open interval on created_at ensured no November records leaked in. Ordering by total_naira descending surfaced the highest-revenue statuses first, making the distribution readable without sorting manually.

Outcome

The status-and-revenue breakdown gave the CFO a starting map of the data, showing where revenue was concentrated and which statuses would need to be excluded from any net revenue calculation.

SELECT 
 status,
 COUNT(*) AS order_count,
 SUM(total_cents) / 100.0 AS total_naira
FROM orders
WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01'
GROUP BY status
ORDER BY total_naira DESC

GROUP BY on status with a descending ORDER BY on the revenue column produces a ranked inventory of all statuses, giving an immediate sense of where value is concentrated.

Next step

Adding a percentage-of-total column for each status's revenue share would make the distribution more immediately comparable across months without requiring a separate calculation.