Jephthah Dashe

Problem

Meridian Retail (simulated retailer) was investigating a revenue gap in October 2023, and a senior analyst needed one verified fact before the deeper work could begin: the exact number of orders placed that month. The question was simple but the stakes were real, because an incorrect count would undermine every calculation built on top of it.

What I did

Rather than filtering on month and year functions, I used a half-open date range in the WHERE clause, setting the lower bound at 2023-10-01 and the upper bound at 2023-11-01 with a strict less-than. This approach safely captures any timestamp that falls late in the day on 31 October without needing to account for time components explicitly. COUNT(*) counts every row regardless of column nullability, which is correct for an order count, and I aliased the result as total_orders so the output column is self-explanatory.

Outcome

The senior analyst received a confirmed baseline count to anchor the revenue gap investigation.

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

The half-open interval ensures no late-day October records are dropped due to a time component on the created_at timestamp.

Next step

A useful follow-up would be to break the 610 orders down by status to see how many were cancelled, returned, or refunded within that month, which would let the senior analyst move directly from the count to a net revenue estimate.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed the October 2023 revenue figures calculated in Excel to cross-check against the finance system. Specifically, she asked for gross revenue covering all order statuses, net revenue excluding cancelled, returned, and refunded orders, and the gap between the two, all formatted as Nigerian naira.

What I did

For gross revenue in B4, I used SUMIFS against the order_date column with DATE(2023,10,1) and DATE(2023,11,1) as the date boundaries, mirroring the half-open range logic I used in SQL so both tools produce comparable numbers. The net revenue formula in B5 extends the same SUMIFS with three additional criteria, each using the not-equal operator to exclude the three non-revenue statuses. The gap in B6 is a live subtraction formula rather than a hardcoded value, so it updates automatically if the source data changes. All three cells use a custom format of NGN with thousand separators, which matches the display requirement without altering the underlying numeric values.

Outcome

Nura received a self-updating Excel sheet she could compare directly against the finance system, with the three figures clearly labelled on a dedicated analysis tab.

Next step

It would be worth calculating the net revenue figure month by month across the full dataset to see whether October's cancellation gap is unusually wide or consistent with the longer-term pattern.

Problem

Before any visual analysis of Meridian Retail's (simulated retailer) revenue discrepancy could be built in Power BI, the data had to be connected and structured. Nura Ibrahim asked for all eight schema tables to be loaded in Import mode so that downstream report work could begin.

What I did

Using Power BI Desktop, I connected to the PostgreSQL endpoint and loaded each table by referencing its schema and item name directly in the M code, which keeps the source path explicit and avoids ambiguity if table names appear in multiple schemas. All eight tables came through with their expected column counts, including the is_duplicate flag on customers and the stock_qty column on inventory that would be needed for backorder analysis. Power BI auto-detected nine relationships, covering the core path from order_items through orders to customers and from order_items through products to categories. Single-direction relationships were left in place as the default, which is appropriate for a star-schema-style model where measures flow in one direction.

Outcome

The connected model gave Nura a foundation to build revenue and inventory visuals without any manual relationship configuration needed at this stage.

let
 Source = PostgreSQL.Database("ep-frosty-glade-ab4k8hyw-pooler.eu-west-2.aws.neon.tech:5432", "meridian_retail"),
 retail_categories = Source{[Schema="retail",Item="categories"]}[Data]
in
 retail_categories

This M code pattern references the table by schema and item name explicitly, which prevents Power BI from loading the wrong table if the same name exists in another schema on the same server.

Next step

The next step would be to verify that the auto-detected relationships use the correct key columns and that cardinality is set appropriately for each join, particularly for the order_items to orders relationship where duplicates on the many side should be expected.

Problem

As part of Nura Ibrahim's revenue discrepancy investigation at Meridian Retail (simulated retailer), the first step was to understand what order statuses existed in the orders table and how many records carried each one. Without this profile, any net revenue calculation could accidentally include or exclude the wrong statuses.

What I did

The query groups every row in the orders table by its status value and counts rows per group using COUNT(*), which is the right choice because it counts all rows regardless of any null values in other columns. Ordering the results by order_count descending puts the most frequent statuses at the top, making the output immediately readable as a ranked list. From the results I was able to identify the three statuses most relevant to the CFO's question: cancelled, returned, and refunded, which together represent the orders that should be excluded from any net revenue figure.

Outcome

Nura had a confirmed list of all status values and their volumes, giving her the exact exclusion criteria needed to build a net revenue calculation.

Next step

A useful follow-on would be to calculate the total revenue associated with each status to quantify exactly how much naira value sits in the cancelled, returned, and refunded buckets, rather than just the order counts.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed a single-query baseline of the orders table before any filtering began: total order count, the earliest and latest order dates, and total gross revenue in naira. This summary would serve as the reference point for all subsequent comparisons in the revenue investigation.

What I did

I calculated the order count with COUNT(*) to capture every row regardless of status, since this is a gross baseline and no filtering is appropriate at this stage. Date range extraction uses MIN and MAX on the created_at column, cast to the date type with ::date so the result displays as a clean calendar date rather than a full timestamp. For revenue, total_cents is summed and divided by 100.0 rather than 100, which forces PostgreSQL into decimal division and prevents integer truncation; wrapping the result in ROUND with two decimal places keeps the figure accurate to kobo precision. All four columns carry descriptive aliases that match the language a stakeholder would use when reading the output.

Outcome

Nura received a single verified row covering all four baseline metrics, ready to be used as the reference denominator for every subsequent filtered calculation 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 is the critical detail here: without the decimal, PostgreSQL performs integer division and silently drops the kobo portion of every summed value.

Next step

The natural next query would apply the same four metrics with a WHERE clause excluding cancelled, returned, and refunded orders, so the gross and net baselines can be compared directly at the full-dataset level rather than just for October.