Idowu Temitope Dominic

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed October 2023 revenue calculated in Excel so she could cross-check against the Finance system. The ask was to separate gross revenue, which includes all order statuses, from net revenue, which excludes cancelled, returned, and refunded orders, and to show the gap between the two figures.

What I did

Both revenue cells use SUMIFS referencing pre-existing helper columns for month and year in the Orders sheet, which avoided any date-parsing complexity while keeping the formulas readable. The net revenue formula applies three simultaneous inequality criteria to exclude the three non-revenue statuses in a single expression. Calculating the gap as `=B1-B5` then made the difference immediately visible. All three cells carry the custom format code `[$NGN -466]#,##0.00` to satisfy the thousand-separator and currency formatting requirement.

Outcome

Nura had a formatted, formula-driven breakdown she could use directly for the Finance system cross-check, with the gap figure labelled in context.

=SUMIFS(Orders!$I:$I, Orders!$P:$P, 10, Orders!$Q:$Q, 2023)

This gross revenue SUMIFS matches rows where the Month column equals 10 and the Year column equals 2023, then sums the total_naira column for all matching orders regardless of status.

Next step

Extending the same SUMIFS structure across all twelve months of 2023 would show whether October's 32 percent gap is typical or an outlier worth escalating.

Problem

Nura Ibrahim, the CFO at Meridian Retail (simulated retailer), needed a verified baseline before filtering the orders data for a revenue investigation. The specific ask was four metrics from the orders table: total order count, earliest and latest order date, and total revenue converted from cent-stored values to naira.

What I did

Targeting the schema-qualified table `retail.orders`, I used `COUNT(*)` for the order total and wrapped both `MIN(created_at)` and `MAX(created_at)` in a `::date` cast to strip timestamps and return clean calendar dates. Total revenue required `SUM(total_cents)/100.0` inside `ROUND(..., 2)` to handle the cents-to-naira conversion with two decimal places. Choosing `created_at` over any other date column was deliberate, the reviewer noted it as the correct column for this table.

Outcome

Nura received a verified single-query baseline she could use as the reference point for every subsequent filter and comparison in the revenue investigation.

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

This query produces all four required baseline metrics in a single pass, with the ::date cast keeping dates readable and the division by 100.0 ensuring correct naira conversion.

Next step

A useful follow-up would be to break total revenue by calendar year to see whether the four-year span shows growth, decline, or a flat trend, which would give the investigation more context before status-level filtering begins.

Problem

Nura Ibrahim, CFO at Meridian Retail (simulated retailer), flagged a revenue discrepancy and assigned an investigation. Before any filtering could begin, the first step was to confirm exactly which order statuses exist in the table and how many orders carry each one.

What I did

A single `SELECT status, COUNT(*) GROUP BY status` query against `retail.orders` was sufficient to enumerate every distinct status and its order count. The schema prefix kept the query scoped correctly. No assumptions were made about which statuses would appear, the query let the data surface all of them, including the less obvious `refunded` and `returned` values the reviewer specifically noted as critical for the investigation ahead.

Outcome

Nura had a confirmed, complete map of all status values before any filtering logic was written, preventing assumptions about which statuses existed from affecting downstream queries.

SELECT STATUS, COUNT(*) AS order_count
FROM retail.orders
GROUP BY STATUS

This query groups every row by its status value and counts them, producing a complete enumeration without any hardcoded assumptions about which statuses exist.

Next step

A next step would be to compare the order counts per status against the corresponding revenue each status carries, to see which statuses are high-volume but low-value versus those driving the bulk of the discrepancy.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed to understand the source of an approximately NGN 18.4 million revenue gap. She asked for order revenue broken down by status, with both a gross total across all statuses and a net total that zeroed out cancelled, returned, and refunded rows.

What I did

Grouping by status and computing `SUM(total_cents)/100.0` gave the gross figure at the row level for each status. The net column uses a `CASE WHEN status NOT IN ('cancelled', 'returned', 'refunded') THEN total_cents ELSE 0 END` expression inside a SUM, which zeroes out the three excluded statuses without removing their rows from the output. This meant Nura could see the excluded statuses alongside their zeroed net values rather than having them disappear from the result. Adding `ORDER BY status` kept the output consistent and easy to read.

Outcome

Nura received a single query result showing both totals side by side for every status, giving her the row-level evidence needed to identify which statuses were driving the discrepancy.

SELECT
 status,
 SUM(total_cents) / 100.0 AS gross_total,
 SUM(
 CASE
 WHEN status NOT IN ('cancelled', 'returned', 'refunded')
 THEN total_cents
 ELSE 0
 END
 ) / 100.0 AS net_total
FROM orders
GROUP BY status
ORDER BY status;

The CASE WHEN inside SUM conditionally includes or zeros each row's total_cents value, so both gross and net figures are computed in a single pass without requiring a subquery or self-join.

Next step

Adding a percentage column showing each excluded status's share of total gross revenue would let Nura prioritise which status type, cancelled, returned, or refunded, to investigate first.

Problem

Before any analysis of the Meridian Retail (simulated retailer) data file could begin, Nura Ibrahim asked for a structured audit of the workbook, covering sheet inventory, column identification in the Orders sheet, distinct order statuses, and at least three data quality issues spotted from inspection.

What I did

Documenting the sheet inventory and column count was a manual inspection step, confirming six sheets and fifteen columns in the Orders sheet. A PivotTable in the Data_Audit sheet was the method used to enumerate the seven distinct order statuses without hardcoding any values. Closer inspection of the data uncovered three quality issues worth flagging: the `legacy_crm_id` column is entirely null across all 1,849 rows, ambiguous product names such as Linen Shirt map to two separate SKUs distinguished only by gender, and 130 customers carry zero orders and zero lifetime value while some customer records fall outside the expected date window.

Outcome

The completed Data Audit sheet gave Nura a documented reference of the file structure and a flagged list of quality issues to address before relying on the data for financial reporting.

Next step

Investigating whether the 130 customers with zero orders are concentrated in a particular signup period or acquisition channel would clarify whether they represent a data entry gap or a genuine retention problem.