Emmanuel Olawale

Problem

With the investor meeting already underway, a senior stakeholder at Meridian Retail (simulated retailer) needed a short, accurate email on his phone giving the clean customer count, one sentence explaining why that number is the right one to use, and a heads-up about the duplicate volume. The deliverable was the formatted email body itself, produced directly from SQL.

What I did

I used a subquery to derive all three counts, total, duplicates, and clean records, in a single pass using COUNT(*) FILTER (WHERE ...) for conditional aggregation. The outer query used format() with to_char() and the FM9,999 mask so the numbers appear comma-formatted and human-readable in the email body. Structuring the prose as number, then explanation, then caveat matched the stakeholder's stated reading context: a phone screen between meetings with no time for re-reading.

Outcome

The stakeholder received a single, self-contained email body with verified numbers and a clear narrative that pre-empted confusion about differing counts from other teams.

SELECT format(
 E'Hi Chidi,\n\nOur clean customer count is %s.\n\nIt''s the right number to use because it counts each real customer once: it starts from all %s records and removes the %s that are flagged as duplicates.\n\nHeads-up so nobody is surprised later: those %s duplicates (about %s%% of records) are why Marketing''s %s runs high, so if anyone quotes that figure, that''s the reason.\n\nRegards,\nEmmanuel ',
 to_char(clean, 'FM9,999'),
 to_char(total, 'FM9,999'),
 dup,
 dup,
 ROUND(100.0 * dup / total),
 to_char(total, 'FM9,999')
) AS email_to_chidi
FROM (
 SELECT
 COUNT(*) AS total,
 COUNT(*) FILTER (WHERE is_duplicate) AS dup,
 COUNT(*) FILTER (WHERE NOT is_duplicate) AS clean
 FROM retail.customers
) AS counts;

The subquery produces all three counts in one scan, and to_char with FM9,999 formatting ensures numbers are comma-separated and readable directly inside the email string.

Next step

The email flags that Marketing may quote the higher raw total, so a useful follow-up would be identifying which specific marketing reports pull from the unfiltered table so those sources can be corrected at the root.

Problem

The senior analyst team at Meridian Retail (simulated retailer) was investigating a revenue gap in October 2023 and needed one verified fact as their starting point. I was asked to count the total number of orders placed in October 2023 using the created_at column in the orders table.

What I did

Rather than using a BETWEEN or EXTRACT approach, I bounded the month with a >= '2023-10-01' AND < '2023-11-01' pattern. This handles any time component on a timestamp column without risk of edge-case misses at the end of the month. The result was aliased as october_orders so the output column is self-explanatory without additional documentation.

Outcome

The senior analysts had a single confirmed order count to anchor their investigation, removing any ambiguity about the starting figure.

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

The half-open interval boundary safely counts every order timestamped within October regardless of time component, and the alias makes the output immediately readable.

Next step

A useful next step would be comparing the 610 October figure against the same month in prior years to judge whether the gap is a volume drop, a pricing shift, or both.

Problem

Before an investor meeting, a senior stakeholder at Meridian Retail (simulated retailer) needed an exact, defensible breakdown of how many customer records were duplicates and how many were clean. The task required live formulas producing three numbers: duplicates, clean records, and total row count, all of which had to sum consistently.

What I did

I used COUNTIF against the is_duplicate boolean column twice, once with TRUE to capture flagged duplicates and once with FALSE to capture clean records. COUNTA on the customer_id column produced the total row count. A cross-check formula subtracting the two counts from the total resolves to zero, confirming internal consistency before the numbers go in front of investors. I also added a live percentage row dividing duplicate count by total, formatted as a percentage, to give instant proportional context.

Outcome

The stakeholder had a verified, formula-driven summary ready for the investor meeting, with a built-in consistency check confirming the three numbers agree.

Next step

It would be worth segmenting the 200 duplicate records by the date they were created to check whether duplication is concentrated in a particular data import or time window.

Problem

A senior stakeholder at Meridian Retail (simulated retailer) needed a quick, glanceable gross revenue figure in naira while a revenue investigation was ongoing. I was asked to connect Power BI to the Meridian database, load the orders table, and build a measure that converts the stored kobo amounts into naira.

What I did

Connecting Power BI directly to the Meridian database and loading the retail orders table gave me a live data source. The DAX measure divided the sum of the amount column by 100 to convert from kobo to naira, and I named it Gross Revenue (NGN) so the unit is explicit in every visual that uses it. The measure was applied to a chart visual, and the resulting value in the roughly NGN 2 billion range confirmed the conversion was working. The submission was marked down because the task required a card visual and I used a bar chart instead.

Outcome

The measure is reusable for any visual on the report page, so correcting the chart type to a card would complete the deliverable without rebuilding the underlying logic.

Next step

Replacing the bar chart with a card visual and pinning the exact confirmed naira total would bring this submission fully in line with the original brief.

Problem

Multiple teams at Meridian Retail (simulated retailer) were reporting different customer counts, and senior analysts were investigating the discrepancy. I was asked to produce the single authoritative clean count by filtering out rows flagged as duplicates in the is_duplicate column.

What I did

The query filters on is_duplicate = FALSE and wraps the result in COUNT(*), returning a single integer. Schema-qualifying the table as retail.customers makes the query unambiguous in a multi-schema environment. The alias clean_customer_count means the output column is self-documenting, which matters when the number is being passed to other analysts as a reference baseline.

Outcome

Senior analysts had a confirmed, reproducible baseline query they could point other teams to when reconciling conflicting customer counts.

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

Filtering on is_duplicate = FALSE before counting excludes all flagged rows in a single pass, and the alias makes the output immediately usable as a reference number.

Next step

Joining the clean customer list to the orders table would show whether any of the 200 duplicate records also have associated orders, which could affect revenue totals if those orders are counted twice.