Oluwashola Adebisi

Problem

Chidi Okafor's team at Meridian Retail (simulated retailer) needed to validate whether the system's duplicate flags were reliable. The specific question was how many name matches were genuine duplicates caught by the system versus coincidental matches involving different people sharing a name.

What I did

To answer this, I added a Name Match Count column using COUNTIF anchored to the full customer range, then a Verified Duplicate column using an AND formula that combined the name-match count with the is_duplicate flag. Using TRUE() rather than the text string 'TRUE' avoided a common comparison pitfall. A Duplicate Summary sheet then pulled five metrics using COUNTIF and COUNTIFS, covering total customers, system-flagged duplicates, name matches, verified duplicates, and unflagged coincidental matches. Beyond the core requirements, I built a Channel Performance sheet with COUNTIFS, AVERAGEIFS, and SUMIFS that excluded duplicate records from channel-level figures.

Outcome

The Duplicate Summary sheet gave Chidi a clear, investor-ready table distinguishing genuine duplicates from coincidental name collisions, removing the risk of misrepresenting system accuracy in the presentation.

Next step

A useful next step would be to break the 240 unflagged name matches down by registration channel to see whether coincidental duplicates cluster in any particular sign-up path.

Problem

Nura Ibrahim (CFO) at Meridian Retail (simulated retailer) flagged a revenue discrepancy and needed a first look at the orders table before deeper investigation. The question was which order statuses existed and how many orders and how much gross value each status carried.

What I did

The query groups orders by status and produces a count and a summed revenue figure for each group. Adding SUM(total_order) alongside COUNT was a deliberate choice to surface the financial weight of each status, not just its frequency. The query also includes a plain-language comment to document intent. The reviewer noted, however, that a date filter referencing a non-existent column was included, which may have broken execution, so the revenue and count values were not confirmed as correct.

Outcome

The approach established a reusable pattern for status-level aggregation that can be applied to the confirmed schema once the invalid column reference is removed.

SELECT
 status,
 COUNT(*) AS order_count,
 SUM(total_order) AS gross_amount
FROM orders
WHERE order_date >= '2023-10-01'
 AND order_date < '2023-11-01'
GROUP BY status
ORDER BY gross_amount DESC;

The GROUP BY with COUNT and SUM correctly aggregates by status; the WHERE clause references a column that does not exist in the table, which the reviewer flagged as potentially breaking the query.

Next step

The immediate correction is to rerun the query against the confirmed date column name to produce verified counts, then compare gross amount by status against the Finance system figure Nura flagged.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed a board-ready summary of the full complaints record. She asked for a total complaint count, a percentage breakdown by category sorted most to least common, and a single sentence identifying which category to lead with.

What I did

The total was calculated with COUNTA referencing the full complaints column, returning 64,737 records. Category counts used COUNTIF against each category label, with percentages derived by dividing each count by the anchored total cell rather than hardcoding any figure. For the board talking point, an INDEX/MATCH/MAX combination identifies the top category dynamically, and a TEXT function embeds the percentage, meaning the cell updates automatically if the underlying data changes. All counts and percentages were verified to sum correctly.

Outcome

Funmi received a fully live summary sheet where every figure recalculates from the source data, giving her team a reusable template rather than a one-off snapshot.

Next step

Breaking complaint counts by month within each category would show whether the lost-items rate is stable or worsening over time, which would sharpen the board message considerably.

Problem

Funmi Alade at FlowRoute Logistics needed to know which hubs posed the greatest operational risk. She asked for delivery rate, raw complaint count, and a volume-normalised complaint rate per hub, with an honest verdict rather than a manufactured ranking.

What I did

Hub attributes were pulled via INDEX/MATCH against the Hub_Performance sheet rather than pasted in, so the table stays live. Complaint counts came from COUNTIF referencing the full Complaints sheet column, and the per-1,000-shipments rate divided each hub's complaint count by its own total shipments figure to remove volume bias. MIN and MAX formulas in a summary spread table made the narrow range across hubs visible at a glance. The verdict explicitly named both the lowest delivery rate hub and the highest complaint-rate hub, and stated that the two signals disagreed, resisting the urge to pick a single scapegoat.

Outcome

The analysis gave Funmi a defensible, evidence-backed answer she could take to stakeholders without overstating risk at any one hub.

Next step

Segmenting the complaint-rate calculation by complaint category per hub would reveal whether certain hubs are disproportionately responsible for a specific failure type, such as lost items, even if their overall rate looks average.

Problem

Nura Ibrahim (CFO) at Meridian Retail (simulated retailer) needed October 2023 revenue calculated in Excel to cross-check against the Finance system. The specific requirement was to separate gross revenue across all statuses from net revenue after excluding cancelled, returned, and refunded orders, and to show the gap between the two.

What I did

Gross revenue used a SUMIFS formula targeting the total_naira column with a date boundary constructed from DATE functions, bounding the range to October 2023 only. Net revenue chained three additional criteria onto the same formula, each excluding one unwanted status with a not-equal operator. The gap in B6 is a live subtraction rather than a typed figure, so all three cells update together if the source data changes. Currency formatting used a custom code to display naira with thousand separators as required.

Outcome

Nura received three reconcilable figures, all matching the Finance system's expected values within rounding tolerance, giving her a confirmed baseline for the revenue discrepancy investigation.

=SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1))
=SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1),Orders!$H:$H,"<>cancelled",Orders!$H:$H,"<>returned",Orders!$H:$H,"<>refunded")

The first formula captures all October orders; the second chains three status exclusions onto the same date filter, producing net revenue in a single pass without needing a helper column.

Next step

Repeating the net revenue calculation at product-category level would show which categories drive the largest share of the NGN 18,426,410 cancellation and return gap, which could point Nura toward a specific fulfilment issue.