Muslimah Raji

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim flagged a revenue discrepancy and needed a starting point before any filtering could begin. The specific question was: what distinct order statuses exist in the orders table, and how many orders carry each status?

What I did

To answer this cleanly, I grouped every row in the orders table by its status column and counted rows per group using COUNT(*). Sorting the result by count descending meant Nura could read volume distribution at a glance without scanning an unsorted list. A column alias, Order_Count, kept the output self-explanatory rather than leaving an unlabelled count column.

Outcome

Nura received a clean, ranked status distribution that established the foundation for separating gross revenue from net revenue in the next steps of the investigation.

SELECT status, COUNT(*) AS "Order_Count"
FROM orders 
GROUP BY status
ORDER BY COUNT(*) DESC

This query groups every row by status, counts each group, and sorts descending so the most common status appears first, giving an immediate sense of order volume distribution.

Next step

A useful next cut would be filtering to a specific date range, such as the month Nura flagged, to see whether the status distribution shifts in that period compared to the full table.

Problem

Still supporting Nura Ibrahim's investigation at Meridian Retail (simulated retailer), the next step was establishing a clean baseline from the full orders table before any filtering was applied. Nura asked for total order count, the full date range, and total revenue in naira in a single summary.

What I did

All four metrics fit cleanly into one SELECT statement with no WHERE clause, since the baseline should cover every row. Casting MIN(created_at) and MAX(created_at) to ::date strips the time component from the timestamp, which makes the date range readable without extra formatting. Dividing total_cents by 100.0 rather than 100 forces PostgreSQL into decimal division before ROUND() is applied, avoiding integer truncation. Descriptive aliases, total_orders, earliest_order, latest_order, and total_revenue_naira, made the output immediately readable without any post-query relabelling.

Outcome

Nura had a single, verifiable baseline row covering total volume, date span, and gross revenue, giving her a fixed reference point for every subsequent filtered comparison in the investigation.

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

Dividing by 100.0 rather than 100 ensures PostgreSQL performs decimal arithmetic before ROUND(), preventing silent integer truncation that would corrupt the naira figure.

Next step

Adding a filtered version of the same query restricted to a specific month would let Nura compare that month's share of gross revenue against the full-period total without changing the baseline query.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) was receiving three different customer counts from three internal teams: 2,331 from marketing, 2,131 from operations, and 2,200 from another source, ahead of an investor meeting. The task was to identify which count was defensible and explain exactly why the others were wrong.

What I did

Rather than writing separate subqueries, I used COUNT(*) FILTER (WHERE ...) to produce all four key counts in a single row, which makes the reconciliation self-contained and easy to verify. Each alias maps directly to a team's counting method: all_customers for marketing's raw total, non_duplicate_customers for the deduplicated count, and active_non_duplicate_customers for finance's activity-filtered count. An inline comment in the submission identified 2,131 as the defensible figure, explaining the business reasoning behind not filtering on order activity when the goal is counting real, unique customers. The gap of 26 between the operations and finance counts, credited by the reviewer, is what reconciles those two figures.

Outcome

Chidi had a single query producing all four counts in one row, with the reconciling gap made explicit, giving him a figure he could defend and an explanation of why the other counts diverge.

select COUNT(*) all_customers,
 COUNT(*) FILTER (WHERE is_duplicate = FALSE) AS non_duplicate_customers,
 COUNT(*) FILTER (WHERE is_duplicate = FALSE AND email IS NOT NULL) AS non_duplicate_with_email,
 COUNT(*) FILTER (WHERE is_duplicate = FALSE AND total_orders > 0) AS active_non_duplicate_customers
from retail.customers;

Using FILTER (WHERE ...) inside COUNT(*) produces all four counts in one pass over the table, making the relationship between each team's counting method visible in a single result row.

Next step

Profiling those 26 zero-order customers further, such as checking their registration date and email validity, would clarify whether they are genuinely new or represent a data quality issue worth flagging before the investor meeting.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed to understand the source of a gap between the gross and net revenue figures reported for October 2023. The question was whether both figures could be produced by status in a single query to show clearly which statuses were driving the difference.

What I did

Filtering to October 2023 with a half-open date range, created_at >= '2023-10-01' AND created_at < '2023-11-01', avoids any ambiguity at month boundaries. For the net revenue column, SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded')) / 100.0 suppresses only the three excluded statuses, returning null for those rows rather than zero, which makes the exclusion visible at a glance. Grouping by status means each row of the result represents one status, so the full picture of gross and net sits side by side per status. Both columns apply the same cents-to-naira conversion by dividing by 100.0.

Outcome

Nura received a per-status breakdown with both figures in naira, giving her the evidence needed to explain the gap to the board before the revenue presentation.

SELECT status,
 SUM(total_cents) FILTER (WHERE status NOT IN ('cancelled','returned','refunded'))/100.0 AS net_revenue,
 SUM(total_cents)/100.0 AS gross_revenue
FROM retail.orders
WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01'
Group by status;

Applying FILTER inside the SUM means net_revenue shows null for excluded statuses rather than zero, making the rows that drive the gap immediately identifiable without a separate filter step.

Next step

Extending the same query across multiple months would reveal whether the NGN 18.4M gap is consistent or whether October had an unusually high volume of cancellations and returns compared to surrounding months.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) was seeing two October 2023 revenue figures that could not both be right: the finance system reported approximately NGN 38.5M while the sales dashboard showed approximately NGN 56.9M. The ask was to produce an October breakdown by order status showing gross and net revenue side by side, so the two systems could be reconciled before a board presentation.

What I did

The query groups October orders by status using the same half-open date boundary used in earlier tasks, covering all 7 statuses. A CASE WHEN expression inside SUM assigns total_cents to net revenue only for statuses outside the cancelled, returned, and refunded group, and defaults to 0 for the others, producing an explicit net figure per status row. Sorting by gross_revenue_naira descending puts the highest-volume statuses at the top. The cents-to-naira conversion divides by 100 in both columns.

Outcome

The approach established the correct reconciliation structure for Nura, though correcting the currency conversion error would be required before the figures could be used in the board presentation.

SELECT status, sum(total_cents)/100 AS gross_revenue_naira, 
 SUM(CASE WHEN status NOT IN ('cancelled','returned','refunded') THEN total_cents ELSE 0 END) / 100 AS net_revenue_naira
FROM retail.orders
WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01'
Group by status
ORDER BY gross_revenue_naira DESC

The CASE WHEN inside SUM assigns the full cent value only for revenue-positive statuses and defaults to 0 for the three excluded ones, producing a per-status net figure in a single pass.

Next step

Before rerunning, confirming whether total_cents stores values in kobo or naira by cross-checking one known order against a source record would prevent the same conversion error from recurring in downstream queries.