Naledi Motsoikha

Problem

Chidi Okafor, CEO at Meridian Retail (simulated retailer), was about to walk into an investor meeting and needed the clean, defensible customer count in a format readable on a phone between meetings. The brief required the number itself, one sentence on why it was trustworthy, and one sentence flagging the duplicate records so no one in the room would be caught off guard.

What I did

Filtering on is_duplicate = FALSE against the retail.customers table produces the system-verified unique count rather than a manual deduplication attempt, which made it the right choice for an investor context where the number has to be auditable. The email structure matched the three-part brief exactly: number first, rationale second, caveat third. Putting the figure in the subject line meant Chidi could see 2,131 before opening the message, which mattered given the time pressure. Keeping the language jargon-free meant the email could be forwarded without translation.

Outcome

Chidi had a single defensible number with a plain-language explanation in time for the investor meeting, and the duplicate caveat ensured no follow-up question about data quality would land as a surprise.

-- Clean customer count for investor - excludes duplicates using system flag
SELECT COUNT(*) AS clean_customer_count
FROM retail.customers
WHERE is_duplicate = FALSE;

The WHERE clause delegates deduplication to the system flag rather than ad hoc logic, making the result reproducible and auditable by any team member who runs the same query.

Next step

It would be worth checking whether the 200 duplicate records share common patterns, such as the same email domain or acquisition source, to understand whether the duplication is a data entry issue or a system import problem.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed to reconcile two conflicting October 2023 revenue figures before presenting to the board: the Finance system was showing one number and the sales dashboard a higher one. The ask was a status-level breakdown with both gross and net revenue visible side by side.

What I did

Using an open-ended date boundary, created_at >= '2023-10-01' AND created_at < '2023-11-01', avoids the edge cases that arise with BETWEEN on timestamp columns. The FILTER (WHERE ...) clause inside SUM keeps gross and net on the same row per status, so the exclusion effect is visible at a glance without a second query or a join. Splitting the calculation this way also made it explicit which statuses contribute nothing to net revenue, turning the reconciliation into a readable explanation rather than just two totals. The query confirmed that gross was NGN 56.88 million and net was NGN 38.45 million across 610 orders.

Outcome

Nura received a status-level table that named each exclusion category with its naira value and explained the mechanical reason for the discrepancy, giving her a complete answer to bring to the board.

SELECT 
 status,
 COUNT(*) AS order_count,
 SUM(total_cents) / 100.0 AS gross_revenue,
 SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled', 'returned', 'refunded')) / 100.0 AS net_revenue
FROM retail.orders
WHERE created_at >= '2023-10-01' 
 AND created_at < '2023-11-01'
GROUP BY status
ORDER BY gross_revenue DESC;

The FILTER clause computes net revenue inline, so each status row shows both its gross contribution and its net contribution without requiring a separate aggregation step or a self-join.

Next step

Comparing October's cancellation and return rates to prior months would show whether the NGN 18 million exclusion gap is typical or unusually large, which is the natural follow-on question once the board has the reconciliation.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was receiving three different customer counts from three different teams and needed a single number he could defend to an investor. The brief asked me to show all three counting methods, identify which was correct, and explain why the others diverged.

What I did

A UNION ALL with a descriptive label column in each SELECT block produces a single result set where each row names its method and shows its count, which means Chidi can hand the output to anyone and it explains itself without a separate document. The three methods map to raw row count, is_duplicate = FALSE for the deduplicated figure, and is_duplicate = FALSE combined with total_orders > 0 for the active-customer slice. Labelling the second method as the investor-ready number inside the query string itself removes ambiguity. Noting that the mystery figure of 2,200 is not reproducible from the data, and that 2,105 is the closest interpretable count, was the kind of analytical honesty the task required.

Outcome

Chidi had a single labelled output he could use to explain every count discrepancy in the room, with the recommended figure identified directly in the result set.

SELECT '1. All rows in customers (Marketing) - 2331' AS counting_method, COUNT(*) AS customer_count
FROM retail.customers

UNION ALL

SELECT '2. Non-duplicate WHERE is_duplicate = FALSE (Operations) - 2131 CORRECT FOR INVESTOR', COUNT(*)
FROM retail.customers
WHERE is_duplicate = FALSE

UNION ALL

SELECT '3. Active non-duplicate WHERE is_duplicate = FALSE AND total_orders > 0 (Finance) - 2105', COUNT(*)
FROM retail.customers
WHERE is_duplicate = FALSE AND total_orders > 0;

The UNION ALL structure stacks all three methods into one result, with the label column carrying the recommendation inline, so no post-processing or separate explanation is needed to interpret the output.

Next step

Tracing where the 200 duplicate records originate, whether from a specific acquisition channel or a bulk import event, would help prevent the same ambiguity from reappearing in future reporting.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a breakdown of customer counts by acquisition source, deduplicated, and sorted by volume so he could speak to acquisition strategy in an upcoming investor meeting. The output needed to be presentation-ready without further formatting.

What I did

Applying is_duplicate = FALSE at the WHERE stage ensures the grouping works only from the 2,131 verified unique customers, keeping the channel totals consistent with the investor figure already established. Grouping by acquisition_source and counting rows produces one row per channel with no manual enumeration needed. Sorting by customer_count DESC means the highest-performing channel leads immediately, which is the natural reading order for a ranked investor table. Aliasing acquisition_source as channel and COUNT(*) as customer_count removes the technical column names from the output.

Outcome

Chidi received a clean, ranked table ready to use directly in an investor conversation, with no additional formatting or explanation required.

SELECT 
 acquisition_source AS channel,
 COUNT(*) AS customer_count
FROM retail.customers
WHERE is_duplicate = FALSE
GROUP BY acquisition_source
ORDER BY customer_count DESC;

The combination of the deduplication filter, grouped count, and descending sort produces a ranked, clean output in a single query with no subquery or post-processing step required.

Next step

Comparing average order value or total revenue by acquisition channel would show whether the highest-volume channels are also the most valuable, which is a natural extension once the investor meeting moves past headcount.

Problem

Nura Ibrahim, CFO at Meridian Retail (simulated retailer), flagged a revenue discrepancy and asked me to investigate by first establishing what order statuses existed in the orders table and how many orders each status held. The specific question was whether the status breakdown could explain why two revenue figures were diverging.

What I did

A simple GROUP BY status with COUNT(*) and ORDER BY order_count DESC was the right starting point because it produces an exhaustive list without assuming which statuses exist. Rather than filtering up front, I kept all rows visible so the full picture was clear before any exclusions were applied. That choice made it straightforward to identify which statuses, specifically cancelled, returned, and refunded, represent non-revenue activity that should be stripped from any net calculation.

Outcome

Nura received a status-by-status count that named the exclusion categories and the size of the gap, giving her a concrete explanation to bring to the board before the deeper reconciliation work began.

SELECT 
 status,
 COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

This query returns every distinct status with its order count, sorted from highest to lowest volume, making it immediately visible which statuses dominate and which represent reversals or cancellations.

Next step

A useful next step would be breaking the non-revenue statuses down by month to see whether cancellation and return volumes are growing, shrinking, or stable over time.