Davis Idiaghe

Problem

While the SQL investigation continued, Nura Ibrahim at Meridian Retail wanted the headline revenue figure independently confirmed using the data file rather than the database. The sales system reported approximately 56.9 million naira in gross revenue for October 2023, and the task was to verify whether the Excel file agreed.

What I did

I built a SUMIFS formula referencing the total_naira column on the Orders sheet with two date conditions bracketing October 2023 exactly: on or after 1 October and before 1 November. Before applying any unit conversion, I checked the column label and confirmed the values were already stored in naira, making a division by 100 unnecessary. The formula sums across rows 2 to 1850, covering the full dataset, and I formatted the result cell with a Nigerian naira currency label so the output would be readable without additional context.

Outcome

The confirmed figure gave Nura a second independent verification of the revenue total, removing any doubt that the sales system was misreporting the month's results.

=SUMIFS(Orders!I2:I1850,Orders!P2:P1850,">=2023-10-01",Orders!P2:P1850,"<2023-11-01")

SUMIFS applies two date boundary conditions on the order date column to isolate October 2023 rows and sum their naira totals, mirroring the same half-open interval logic used in the SQL approach.

Next step

Breaking down October revenue by product category in the same file would show which lines drove the gap the senior team was investigating, rather than only confirming the aggregate.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was about to enter an investor meeting and needed one clean number: the verified customer count with a brief explanation of why it was trustworthy and a flag about the duplicate records. The message had to be short enough to read on a phone between meetings.

What I did

Three separate queries established the figures: filtering on is_duplicate = false gave the clean count, COUNT(customer_id) on the full table gave the total, and filtering on is_duplicate = true isolated the duplicate volume. Running them separately kept each result legible during review. The email was then structured around the brief's three requirements in the same order Chidi stated them: the clean number first, one sentence on why it was the right figure to quote, and one sentence flagging the duplicates so no one in the meeting would be caught off guard.

Outcome

Chidi received a self-contained, phone-readable message with all three figures and the context needed to use the clean number confidently in front of investors.

select COUNT(*) as customer_nonduplicate_count
 from retail.customers
 where is_duplicate = false;

select COUNT(customer_id) as customer_total_count
 from retail.customers;

select COUNT(customer_id) as customer_duplicate_count
 from retail.customers
 WHERE is_duplicate = true;

Three focused queries isolate the clean count, total count, and duplicate count separately, making each figure independently verifiable before it is quoted in the email.

Next step

It would be worth investigating when the 200 duplicate records were created and whether they cluster around a specific data import event, which would help the team prevent the same issue recurring.

Problem

The senior analytics team at Meridian Retail (simulated retailer) was investigating a revenue gap in October 2023 and needed a single verified fact to anchor their work. Nura Ibrahim asked for the total order count for that month, drawn from the created_at column in the orders table. The question was precise: one number, confirmed correct.

What I did

To capture every October row without risk of missing end-of-day timestamps, I used a half-open interval: created_at >= '2023-10-01' AND created_at < '2023-11-01'. This avoids the edge-case behaviour that BETWEEN can produce with timestamp columns. COUNT(*) was the right aggregate here because it counts all rows regardless of NULLs, which matters for an order count where no filtering on a specific column was needed. I aliased the result october_total_orders so the output column would be self-explanatory without any supporting documentation.

Outcome

The verified count gave the senior analysts a confirmed starting point for their revenue gap investigation, removing any ambiguity about the month's order volume.

select COUNT(*) as October_Total_Orders
from orders
WHERE created_at >= '2023-10-01'
AND created_at < '2023-11-01'
;

The half-open interval pattern safely handles any time-of-day component on created_at, ensuring no October rows are missed or double-counted.

Next step

A useful follow-up would be comparing October 2023 order count against the same month in prior years to determine whether 610 represents a genuine drop or normal seasonal variation.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed three things for a board presentation: the total complaint count, a ranked breakdown by category with percentages, and a single sentence identifying the leading complaint type. The data sat in the logistics.complaints table with one row per complaint.

What I did

A preliminary SELECT * gave me a feel for the table structure before writing the aggregation. The total count query ran first to establish the denominator. For the category breakdown, I used a subquery inside the percentage calculation rather than a CTE, which kept the query compact while correctly computing each category's share of the total. Sorting by count descending matched Funmi's explicit instruction to show the most common issues first.

Outcome

Funmi received a ranked table ready to paste into the board pack, with percentages calculated and the dominant category identified.

SELECT
 category,
 COUNT(*) AS number_of_category,
 ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM logistics.complaints), 2) AS pct_complaints
FROM logistics.complaints
GROUP BY category
ORDER BY number_of_category DESC;

The subquery in the percentage column recalculates the total inline so the percentage is always relative to the full complaint population, not just the current group.

Next step

Given how evenly the four categories are distributed, a time-series breakdown by month would reveal whether any single category is growing, which would be a more urgent board-level finding than the near-equal shares the snapshot shows.

Problem

Different teams at Meridian Retail were quoting different customer totals, and Chidi Okafor needed a single authoritative number to anchor the investigation. The task was to count only the rows in the customers table where is_duplicate is false and confirm that number as the foundation every other team's figure should reconcile against.

What I did

The query is short by design: COUNT(*) with a WHERE clause filtering on is_duplicate = false, referencing the full retail.customers schema path to avoid any ambiguity about which database and schema were being queried. Using COUNT(*) rather than COUNT(customer_id) means the count is not affected by any NULLs in that column. The finding was framed directly in terms of the business question, naming the result as the baseline other counts should match rather than presenting it as an isolated number.

Outcome

Chidi had a single confirmed figure to hand to each team as the reference point, making the discrepancy investigation concrete rather than open-ended.

select COUNT(*) 
from retail.customers
WHERE is_duplicate = false;

Filtering on the boolean is_duplicate column before counting ensures only verified, non-duplicate customer records contribute to the total.

Next step

Knowing the count of clean records is useful, but identifying which teams' figures differ from 2,131 and by how much would make the reconciliation actionable rather than descriptive.