Harrison Osiezagha

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was receiving three different customer totals from three internal teams and needed one defensible number for an upcoming investor meeting. The specific question was which of the figures, 2,331, 2,131, or 2,105, was correct, and why the others differed.

What I did

Using COUNT(*) with conditional FILTER clauses reproduces all three counting methodologies in a single pass over retail.customers, so the relationship between the figures is arithmetically transparent in one result row. The FILTER (WHERE is_duplicate = FALSE) isolates non-duplicate records for the operations count, and adding AND total_orders > 0 to that filter replicates finance's stricter rule. Embedding the recommendation as a string literal in a chidi_recommendation column makes the query self-documenting, the output tells Chidi what number to use and why without a separate write-up.

Outcome

Chidi received a single query result containing all three counts, a clear label identifying the correct investor-facing figure, and the logical basis for why the other two differ.

SELECT
 COUNT(*) AS marketing_count,
 COUNT(*) FILTER (WHERE is_duplicate = FALSE) AS operations_count,
 COUNT(*) FILTER (
 WHERE is_duplicate = FALSE
 AND total_orders > 0
 ) AS finance_count,
 '2,131 operations_count represents unique, non-duplicate customers.' AS chidi_recommendation
FROM retail.customers;

The FILTER clauses apply row-level conditions to COUNT without affecting the FROM clause, so all three counts are produced from one table scan and remain directly comparable.

Next step

A follow-on query breaking the 26 non-duplicate zero-order customers down by acquisition source and signup date would help Chidi decide whether those customers are recent signups who may still convert or long-inactive accounts that should be removed from forecasts.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was heading into an investor meeting where three teams were each reporting a different customer total. Marketing, operations, and finance each used a different counting rule, and Chidi needed a single sheet that explained why the numbers differed and which figure to use in which context.

What I did

Each of the three formulas targets a different filtering layer: COUNTA on the full customer column captures every signup regardless of quality; COUNTIF on is_duplicate filters that down to non-duplicated records; COUNTIFS combines the deduplication flag with a total_orders greater than zero condition to match finance's stricter definition. Subtracting 1 from the COUNTA result removes the column header from the row count cleanly. The labels in column D were written to explain not just what each number is but why it is smaller than the one above it, so Chidi could walk an investor through the logic without a separate briefing note.

Outcome

Chidi received a formatted validation sheet with all three counts, their formulas, and plain-language labels explaining the methodology behind each figure.

Next step

Breaking down the 2,131 deduplicated customers by acquisition source would let Chidi show the investor not just how many customers exist but where the highest-quality ones came from.

Problem

The CFO at Meridian Retail (simulated retailer), Nura Ibrahim, flagged a revenue discrepancy and needed a starting point before any filtering began. The immediate question was: what order statuses exist in the orders table, and how many orders carry each one? This frequency profile would shape every subsequent step of the investigation.

What I did

A simple GROUP BY on the status column paired with COUNT(*) is the cleanest way to answer a frequency question without touching any other table. I aliased the count as order_count to keep the result readable, and added ORDER BY order_count DESC so the dominant status appears first, removing the need for a stakeholder to scan the rows themselves. Qualifying the table as retail.orders makes the query portable and unambiguous regardless of the session search path.

Outcome

Nura received a complete, sorted status inventory that confirmed no statuses were missing and identified the largest status bucket, giving the investigation a concrete baseline to filter from.

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

This query groups every row in retail.orders by its status value and counts how many rows share each one, sorted so the highest-volume status appears first.

Next step

A useful follow-on would be to break each status down by month to see whether the share of cancelled or returned orders has shifted over time, which might point to when the discrepancy started.

Problem

Still supporting Nura Ibrahim's revenue investigation at Meridian Retail (simulated retailer), the next requirement was a single-query baseline: total order count, the earliest and latest order dates, and total gross revenue in naira. This summary would anchor all subsequent filtered analyses.

What I did

Combining COUNT(*), MIN, MAX, and SUM into one SELECT means the baseline is produced in a single pass over the table, which also ensures all four metrics are always consistent with each other. Casting the date columns to ::DATE strips the time component and makes the output immediately readable in a report. Revenue was stored in the database as integer cents, so dividing by 100.0 and wrapping the result in ROUND with two decimal places converts it accurately to naira without floating-point drift. Each alias was written to be self-explanatory so the result set needs no column key.

Outcome

Nura received a single clean row of four baseline metrics that could be used as the reference point for every filtered or segmented query in the investigation.

SELECT
 COUNT(*) AS total_orders,
 MIN(created_at)::DATE AS earliest_order_date,
 MAX(created_at)::DATE AS latest_order_date,
 ROUND(SUM(total_cents) / 100.0, 2) AS total_revenue_naira
FROM retail.orders;

The cents-to-naira division uses 100.0 rather than 100 to force a decimal result before ROUND is applied, which avoids integer truncation on the SUM.

Next step

Splitting this same summary by order status would immediately show how much of the NGN 2,305,853,142.45 gross figure sits in delivered versus cancelled or returned orders, which is the next logical step in explaining Nura's discrepancy.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a breakdown of customer volume by acquisition source ahead of an investor meeting focused on acquisition strategy. The output had to exclude duplicate records and be sorted so the highest-volume channels appeared first.

What I did

Filtering with WHERE is_duplicate = FALSE before grouping ensures duplicates are excluded from every count, not just the total. The window function SUM(COUNT(*)) OVER () computes the grand total across all groups within the same query pass, which means the percentage column is calculated without a subquery or a second table reference. Wrapping that ratio in ROUND at two decimal places keeps the output tidy for a presentation context. All 10 acquisition sources appear in the result, and the percentages summing to 100 percent confirms the window logic is complete.

Outcome

Chidi received an investor-ready channel breakdown that ranked sources by volume and showed each one's percentage contribution, without requiring any manual calculation outside the query.

SELECT
 acquisition_source,
 COUNT(*) AS customer_count,
 ROUND(
 100.0 * COUNT(*) / SUM(COUNT(*)) OVER (),
 2
 ) AS percentage_of_total
FROM retail.customers
WHERE is_duplicate = FALSE
GROUP BY acquisition_source
ORDER BY customer_count DESC;

SUM(COUNT(*)) OVER () is a window function that sums the per-group counts across all rows in the result, giving a denominator for the percentage without a correlated subquery.

Next step

Adding average total_orders or average lifetime spend per acquisition source would show Chidi not just which channels bring the most customers but which bring the most valuable ones.