Olubisi Davies

Problem

The senior analysts at Meridian Retail (simulated retailer) were investigating a revenue gap in October 2023 and needed one verified fact before going further. The stakeholder asked me to confirm the total number of orders placed during that calendar month using the orders table and its created_at column.

What I did

To avoid any edge case where a timestamp on 31 October could be excluded or included incorrectly, I used a half-open date interval: created_at >= '2023-10-01' AND created_at < '2023-11-01'. This pattern captures every row in the month regardless of the time component on the final day. COUNT(*) was the right aggregate here because every row in the orders table represents one order, so no column filtering was needed.

Outcome

The senior analysts had a confirmed, reproducible order count to anchor their revenue gap investigation.

SELECT COUNT(*)
FROM orders 
WHERE created_at >= '2023-10-01'
AND created_at < '2023-11-01';

The half-open interval on created_at ensures the full month is captured without relying on date-truncation functions, making the filter portable across different SQL dialects.

Next step

A useful next step would be breaking the 610 orders down by order status, to check whether cancellations or returns contributed to the revenue gap the seniors were tracking.

Problem

The stakeholder at Meridian Retail (simulated retailer) wanted the October 2023 gross revenue figure confirmed independently from the sales system, which reported approximately 56.9 million naira for the month. The amounts in the data file are stored in kobo, requiring a unit conversion as part of the calculation.

What I did

My SUMIFS formula targets the total_naira column and applies two date boundary conditions built with DATE(2023,10,1) and DATE(2023,11,1), mirroring the same half-open interval logic used in the SQL task. Rather than hardcoding any value, I kept the formula live so it would recalculate automatically against the underlying data. A companion COUNTIFS in the same sheet let me sense-check that the date filter was catching the expected number of rows, giving me a quick consistency check between the two results.

Outcome

The stakeholder had an independently verified revenue figure, produced with a live formula that could be refreshed if the underlying data file was updated.

Next step

Breaking the SUMIFS down by a product category column, if one exists in the sheet, would show which lines are driving October revenue and whether the gap the seniors are investigating is concentrated in a particular segment.

Problem

While a revenue investigation was ongoing at Meridian Retail (simulated retailer), the stakeholder needed a single number visible at a glance. The task was to connect Power BI to the Meridian database, import the orders table, and display total gross revenue in naira on a clearly titled card visual.

What I did

After connecting to the database and importing the orders table, I created a measure that divides the sum of the kobo-denominated amounts by 100 to produce a naira figure. The card visual was placed on a page titled 'Total Gross Revenue', and the card itself carries a subtitle label of 'Gross Revenue' so the figure is self-explanatory without any surrounding context. Displaying the NGN currency notation directly on the card ensures the number cannot be misread as a kobo figure.

Outcome

The stakeholder could glance at a single, clearly labelled card to confirm the revenue magnitude while the deeper investigation continued.

Next step

Adding a date slicer to the page would let the stakeholder filter the card to October 2023 specifically, making it directly comparable to the monthly figures being investigated.

Problem

A stakeholder at Meridian Retail (simulated retailer) was walking into an investor meeting and needed a single, trusted customer count with a brief note on why it was reliable and a flag about the duplicate records. The email had to be short enough to read on a phone between meetings.

What I did

The SQL query I had already written split the customers table into total rows, duplicate rows, clean customers, and a percentage overstatement, so all four figures were verified before drafting the email. Rather than attaching the query or explaining methodology in detail, I put the clean number in the subject line itself so the stakeholder would have the answer without opening the message. The body followed the three-part structure the task required: state the clean count, explain in one sentence why it is the right number, and flag the duplicates with quantified context.

Outcome

The stakeholder had a verified, phone-readable summary that could be quoted directly in the investor meeting, with the duplicate risk flagged so it would not surface as a surprise.

SELECT
 COUNT(*) AS total_rows,
 SUM(CASE WHEN is_duplicate = TRUE THEN 1 ELSE 0 END) AS duplicate_rows,
 SUM(CASE WHEN is_duplicate = FALSE THEN 1 ELSE 0 END) AS clean_customers,
 ROUND(100.0 * SUM(CASE WHEN is_duplicate = TRUE THEN 1 ELSE 0 END) / COUNT(*), 1)
 AS pct_overstated
FROM Customers;

The CASE WHEN aggregations derive all four figures in a single pass over the table, so the percentage and the counts are guaranteed to be internally consistent with each other.

Next step

It would be worth checking whether the duplicate rows cluster around a specific acquisition channel or signup date, which would help the data team understand how the duplicates were introduced.

Problem

Different teams at Meridian Retail (simulated retailer) were reporting different customer counts, and the senior analysts needed a single, trusted baseline number. The task was to query the customers table, filter on is_duplicate = FALSE, and return the one count that all other comparisons could be measured against.

What I did

The query is deliberately minimal: filtering on is_duplicate = FALSE isolates every row the data team has confirmed as a legitimate customer record, and COUNT(*) over that filtered set produces the foundation number. Aliasing the output as clean_customer_count means anyone reading the query result or picking up the work later knows immediately what the figure represents. Keeping the query simple also means there is no ambiguity about what logic produced the number.

Outcome

Senior analysts had a reproducible, clearly aliased query they could share across teams to resolve the count discrepancy.

SELECT COUNT(*) AS clean_customer_count
FROM customers
WHERE is_duplicate = FALSE;

Filtering before aggregating means COUNT(*) never sees the duplicate rows, so the result is the clean count rather than a derived subtraction that could mask data issues.

Next step

Comparing the 2,131 clean customers against order history would show how many of the duplicate records have associated orders, which matters for revenue attribution accuracy.