Eniola Oyedele

Problem

Before any analysis could begin on the Meridian Retail (simulated retailer) dataset, I was asked to familiarise myself with the Excel workbook and document its structure and quality issues. The task required identifying the number of sheets, listing all columns in the Orders sheet, enumerating distinct order statuses, and flagging at least three data quality concerns in a new Data Audit sheet.

What I did

I worked through each of the six sheets systematically, recording names, column headers, and row counts before drawing any conclusions. For the Orders sheet, I counted all 16 columns and identified all 7 distinct status values by browsing and filtering. The audit table I built used three columns, Issue, Evidence, and Recommended check, to keep findings actionable rather than just descriptive. One well-evidenced issue I flagged was that the legacy_order_ref column was entirely empty across all 1,849 order rows, a concrete finding with a specific column and a verifiable count.

Outcome

The Data Audit sheet gave a structured starting point for the investigation, with the legacy_order_ref finding flagging a column that could be dropped or investigated before any joins or filters rely on it.

Next step

The reviewer noted that two of my three flagged issues were vague or incorrect, so a specific next step would be to return to the Customers or Products sheets and use Excel filters to find columns with partial missingness rather than complete blanks.

Problem

The CFO at Meridian Retail (simulated retailer) was beginning a revenue discrepancy investigation and needed a reliable baseline before any filtering began. I was asked to return four metrics from the orders table in a single query: total order count, earliest and latest order dates, and total revenue converted from cents to naira.

What I did

I used COUNT(*) for the total order count and paired MIN and MAX on the created_at column to capture the full date span without any manual inspection. Converting stored cent values required dividing the SUM of total_cents by 100.0 rather than 100, which forces floating-point arithmetic and avoids integer truncation. Wrapping that expression in ROUND with two decimal places produced a clean, currency-ready result. Because the retail search path was already set in the session, querying the orders table directly was sufficient without schema qualification.

Outcome

The CFO had a verified, single-query baseline to anchor the investigation, confirming the full scope of the dataset before any status or date filters were applied.

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

This query returns all four required metrics in one pass, with the division by 100.0 ensuring the cents-to-naira conversion uses floating-point arithmetic before rounding.

Next step

A useful follow-on would be to break this summary down by calendar year to see whether order volume and revenue grew, plateaued, or declined across the four-year window.

Problem

Nura Ibrahim, CFO of Meridian Retail (simulated retailer), had flagged a revenue discrepancy and needed to understand the composition of orders before investigating further. I was asked to retrieve all distinct order statuses and the count of orders in each, scoped to October 2023.

What I did

I grouped by status and used COUNT(*) per group to satisfy the base requirement. Going beyond what was asked, I added SUM(total_cents) / 100.0 as a total_naira column so the CFO could see revenue weight alongside order volume, which is more directly useful for a discrepancy investigation than counts alone. The date filter uses a half-open interval, created_at >= '2023-10-01' AND created_at < '2023-11-01', which correctly captures all October timestamps including those with non-midnight times while remaining index-friendly. Results were ordered by total_naira descending to surface the highest-revenue statuses first.

Outcome

The CFO could see both order volume and revenue weight per status in a single result, providing the context needed to identify which statuses were contributing to the discrepancy.

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;

The half-open interval on created_at safely captures all timestamps within October without risk of including November records, and ordering by total_naira descending prioritises the most financially significant statuses.

Next step

Adding a percentage-of-gross column for each status would let the CFO see at a glance what share of October revenue each status represents, without needing to calculate it manually.

Problem

Nura Ibrahim, CFO of Meridian Retail (simulated retailer), needed to explain a gap between two revenue figures before presenting to the board. I was asked to produce gross revenue, net revenue excluding cancelled, returned, and refunded orders, and the explicit difference between the two, all for October 2023.

What I did

I used a single-scan approach with two CASE WHEN expressions inside SUM, one summing total_cents only for non-excluded statuses and one summing only the excluded statuses, avoiding the need for subqueries or self-joins. Each monetary result was divided by 100.0 and wrapped in ROUND with two decimal places for clean naira output. The October scope was enforced with the same half-open interval pattern used in earlier tasks. The reviewer noted that the three headline figures were correct but that a per-status breakdown was also expected and was missing from my submission.

Outcome

The CFO received a verified reconciliation of the two conflicting figures, confirming that the difference was explained by excluded order statuses rather than a data error.

SELECT
 ROUND(SUM(total_cents) / 100.0, 2) AS gross_revenue_naira,
 ROUND(
 SUM(
 CASE
 WHEN status NOT IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END
 ) / 100.0,
 2
 ) AS net_revenue_naira,
 ROUND(
 SUM(
 CASE
 WHEN status IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END
 ) / 100.0,
 2
 ) AS revenue_gap_naira
FROM orders
WHERE created_at >= '2023-10-01'
 AND created_at < '2023-11-01';

The two CASE WHEN blocks split the same rows into net-contributing and gap-contributing buckets within a single table scan, producing all three figures without any subquery overhead.

Next step

The missing piece in this submission was a GROUP BY status added alongside the aggregates, which would have shown the CFO exactly how much each excluded status contributed to the NGN 18,426,410.13 gap.

Problem

Nura Ibrahim, CFO of Meridian Retail (simulated retailer), needed to reconcile two conflicting October 2023 revenue figures before a board presentation: a finance system figure of approximately NGN 38.5 million and a sales dashboard figure of approximately NGN 56.9 million. She asked for the October orders broken down by status with both gross and net revenue visible.

What I did

My query used the same CASE WHEN structure from the previous task to split gross and net revenue in one pass, with the half-open date interval keeping the October scope exact. Dividing by 100.0 and rounding to two decimal places produced correctly formatted naira values. The reviewer confirmed all three revenue totals were accurate. However, the submission returned a single aggregated row rather than grouping by status, which was explicitly required by the brief, and that gap accounts for the score reduction.

Outcome

The CFO had verified numbers to anchor the board presentation, though the absence of a per-status row meant she could not see the individual contribution of each excluded status from this output alone.

SELECT
 ROUND(SUM(total_cents) / 100.0, 2) AS gross_revenue_naira,
 ROUND(
 SUM(
 CASE
 WHEN status NOT IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END
 ) / 100.0,
 2
 ) AS net_revenue_naira,
 ROUND(
 SUM(
 CASE
 WHEN status IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END
 ) / 100.0,
 2
 ) AS revenue_gap_naira
FROM orders
WHERE created_at >= '2023-10-01'
 AND created_at < '2023-11-01';

The CASE WHEN expressions inside each SUM cleanly partition rows into gross and net buckets within a single scan, but without a GROUP BY status clause the result collapses to one row rather than one row per status.

Next step

Adding GROUP BY status to this query and including status as a selected column would have satisfied the brief fully and shown the CFO exactly which of the three excluded statuses drove the largest share of the gap.