Janet Joshua

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) was investigating a NGN 18.4M discrepancy between gross and net revenue for October 2023. The specific question was: which order statuses account for the gap, and what are the gross and net totals in a single, readable output?

What I did

To answer both questions in one result set, I used GROUP BY ROLLUP(status), which generates a grand-total row automatically alongside the per-status rows. The net revenue column uses a FILTER (WHERE status NOT IN ('cancelled','returned','refunded')) clause on the same SUM, so both figures come from a single table scan. Storing amounts in cents required dividing by 100.0 rather than 100 to avoid integer truncation before any rounding. Ordering with status NULLS LAST places the rollup grand-total row at the bottom, keeping the output easy to read top to bottom.

Outcome

Nura received a single query result showing per-status revenue and an automatic grand total, giving her a fully documented breakdown of the gap she could share with the broader team without further manipulation.

SELECT 
status,
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 ROLLUP(status)
ORDER BY status NULLS LAST;

ROLLUP(status) adds a null-status grand-total row automatically, and NULLS LAST pushes it to the bottom so the per-status rows read cleanly before the summary.

Next step

A useful follow-up would be breaking the cancelled orders down by product category to see whether cancellations are concentrated in specific inventory segments rather than spread evenly.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed a baseline snapshot of the orders table before any filtering began. The ask was four specific metrics: total order count, earliest and latest order dates, and total revenue in naira.

What I did

COUNT(*) captures all rows regardless of nulls, which is correct for a true baseline count. Casting with ::date strips the time component from the created_at timestamp, so the date range reads cleanly without time noise. Dividing total_cents by 100.0 rather than the integer 100 preserves the decimal before ROUND applies, preventing truncation of the kobo portion. Qualifying the table with the retail schema keeps the query unambiguous in a multi-schema environment.

Outcome

Nura had a confirmed, clean baseline she could reference before applying any status or date filters in the investigation that followed.

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

The ::date cast on MIN and MAX trims the time portion from the timestamp column, and dividing by 100.0 ensures floating-point arithmetic so ROUND receives a decimal value rather than a truncated integer.

Next step

Adding a breakdown of order count and revenue by year would show whether the dataset is evenly distributed across the four-year window or concentrated in specific periods, which matters before drawing any trend conclusions.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a channel-by-channel customer count before an investor meeting. The requirement was explicit: exclude known duplicate records so the figures reflect real, unique customers only, then sort by volume so the highest-performing channels are immediately visible.

What I did

Filtering with WHERE is_duplicate = FALSE before grouping was the critical first step; without it, 200 duplicate rows would inflate every channel's count. After that filter, COUNT(*) at the acquisition_source grain gives an accurate per-channel headcount. Sorting by customer_count DESC surfaces the leading channels without any manual reordering of the output. The query covers all 10 acquisition channels and totals 2,131 non-duplicate customers.

Outcome

Chidi had a defensible, duplicate-free channel breakdown ready for the investor conversation, with results sorted so the strongest channels were immediately legible.

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

The WHERE clause runs before GROUP BY, so only real customers enter the aggregation and each is counted exactly once under their true acquisition source.

Next step

Breaking each channel's count down by signup cohort month would show whether a channel's volume is growing, flat, or shrinking over time, which is a more useful investor signal than a static total.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was preparing investor materials and needed to show customer growth over time, not just a point-in-time count. The request was for monthly new customer acquisition across the last 24 months, with duplicate records excluded.

What I did

DATE_TRUNC('month', created_at) bucketed each customer into their acquisition month without losing the ability to sort chronologically. The 24-month window was applied with an INTERVAL expression on the customers table, which holds the actual acquisition dates. Beyond the required monthly count, I added a SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)) window function to produce a cumulative customer total alongside each monthly figure. That running total makes the growth trajectory visible in a single result set without any additional processing.

Outcome

Chidi received a result set that told two stories at once: how many customers joined each month, and how total acquisition had built over time, giving the investor conversation a concrete growth narrative.

SELECT
DATE_TRUNC('month', created_at) AS acquisition_month,
COUNT(*) AS new_customers,
SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS cumulative_customers
FROM retail.customers
WHERE is_duplicate = FALSE
 AND created_at >= NOW() - INTERVAL '24 months'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY acquisition_month ASC;

The window function SUM(COUNT(*)) OVER produces a running total by ordering the already-grouped monthly counts, so both the per-month figure and the cumulative figure appear in the same row without a self-join.

Next step

Splitting the monthly trend by acquisition channel would show whether overall growth is driven by one dominant source or spread across channels, which is a more precise claim for an investor audience.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was receiving three different customer counts from three different teams and needed to understand which was correct before an investor meeting. The task was to reproduce all three figures in a single query and identify the right one to defend.

What I did

Using three COUNT(*) FILTER clauses on a single scan avoided subqueries or joins while producing all three figures at once. The first clause applied no filter, returning the raw row count that marketing was using. Adding WHERE is_duplicate = FALSE gave the deduplicated count that operations reported. A third clause combined the duplicate filter with a check for total_orders > 0 to isolate the active, non-duplicate count finance was quoting. Structuring the query this way made the relationship between the three numbers transparent in one result.

Outcome

Chidi had a single query output that explained all three numbers and identified 2,131 as the figure to use, with the logic documented in the query structure itself.

SELECT 
COUNT(*) AS all_rows,
COUNT(*) FILTER (WHERE is_duplicate = FALSE) AS flagged,
COUNT(*) FILTER (WHERE is_duplicate = FALSE AND totaL_orders > 0) As active_non_duplicate
FROM customers;

Three FILTER clauses on one COUNT function return all three competing counts in a single pass, making the differences between them immediately comparable without any joins or subqueries.

Next step

Looking at the 26 non-duplicate customers with zero orders by their acquisition channel and signup date would show whether a specific channel or time period is producing unengaged signups.