Emmanuel Badmus

Problem

Nura Ibrahim, CFO at Meridian Retail (simulated retailer), flagged a revenue discrepancy and needed an orientation step before deeper investigation could begin. The specific question was: what distinct order statuses exist in the orders table, and how many orders carry each one? This gave the investigation a factual baseline about how orders were classified.

What I did

The query targets retail.orders directly and uses COUNT(*) as order_count, which is appropriate because every row in that table represents exactly one order. Grouping by status at that grain produces one row per distinct status value with no double-counting. Adding ORDER BY order_count DESC meant the output was immediately readable, with the most frequent status at the top rather than in arbitrary order.

Outcome

Nura received a complete, ordered snapshot of how orders are classified, giving the revenue investigation a reliable starting point for scoping further queries.

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 occurrences, then sorts the result by frequency so the most common statuses appear first.

Next step

A logical next step would be to filter this breakdown by a specific date window, such as October 2023, to see whether the status distribution in that month differs from the overall pattern.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed a visual analysis of the revenue discrepancy, which required all relevant data to be loaded into Power BI before any visuals could be built. The task was to connect Power BI Desktop to the Meridian PostgreSQL database in Import mode and load eight specified tables with correct relationships.

What I did

Each of the eight tables was connected using minimal, schema-qualified M code pointing to the correct PostgreSQL host and database, keeping the Power Query steps clean and easy to audit. Import mode was used as specified, pulling a snapshot of the data into the model rather than querying live. Power BI automatically detected nine relationships across the tables, and the logic holds: order items join to orders and products, orders join to customers and stores, and so on. The relationship from retail sessions to retail customers via customer_id was left inactive because sessions already connect to customers through orders, and activating it would create an ambiguous filter path.

Outcome

The data model was ready for visual development, with every required table in place and relationship logic that supports accurate cross-table filtering.

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

This M code connects to the Meridian PostgreSQL instance and extracts the retail.categories table by schema and item name, which is the pattern repeated for each of the eight loaded tables.

Next step

With the model in place, the next step would be to verify that the automatically detected relationship cardinalities are correct, particularly the one-to-many direction between orders and order_items, before building any measures on top of them.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted the headline October 2023 revenue figure verified independently while senior analysts investigated the broader discrepancy. The sales system reported approximately 56.9 million naira for the month, and the task was to check whether the exported data file agreed.

What I did

The work was organised on a dedicated sales_value sheet to keep the answer clearly separated from the raw data. The formula sums column I of Sheet1 across rows 1 to 611, which covers the header row and all 610 October orders. Using the pre-converted total_naira column meant no manual kobo-to-naira conversion was needed. A Nigerian naira currency format (NGN) was applied to the result cell to make the output professionally readable.

Outcome

Nura received an independently verified revenue figure that confirmed the sales system's claim, giving the investigation a trusted baseline to work from.

=SUM(Sheet1!I1:I611)

This formula sums all values in the total_naira column across the header and 610 October order rows, producing the gross revenue figure on the dedicated answer sheet.

Next step

A useful follow-on would be to break the NGN 56,876,792.48 total down by order status, to see what portion of that gross figure sits in cancelled or refunded orders and therefore may not represent collected revenue.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a verified customer count to use in an investor meeting and asked for it by email, formatted for quick reading on a phone. The email had to state the clean count plainly, explain briefly why it was the right number, and flag the duplicate records so investors would not be caught off guard.

What I did

The email opens with the clean count of 2,131 immediately, putting the most important fact first for a reader scanning on a mobile screen. The explanation sentence avoids jargon: duplicates were removed and the figure reflects unique customers only. The caveat sentence packages all three facts Chidi would need to answer an investor question, the original record count of 2,331, the number of duplicates removed (200), and the implication that using the raw count would overstate the customer base. Subject line, greeting, short body, and sign-off kept the structure conventional and clean.

Outcome

Chidi had a single, mobile-readable email with the correct number and enough context to address follow-up questions from investors without needing to consult anyone else.

Next step

It would be worth examining the 200 duplicate records more closely to determine whether they cluster around a particular acquisition source or customer segment, which could indicate a specific data entry problem upstream.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed one verified fact to anchor the senior analysts' revenue gap investigation: the total number of orders taken in October 2023. The orders table holds a created_at timestamp column, and the task was to count rows where that column falls within the calendar month.

What I did

Rather than using BETWEEN or an upper bound of '2023-10-31', the query uses a half-open interval: created_at >= '2023-10-01' AND created_at < '2023-11-01'. This approach handles timestamp values that fall on the last day of the month without risk of missing records with a time component after midnight. The result is aliased as total_orders so the single-column output is self-describing without requiring a separate explanation.

Outcome

The senior team had a confirmed order count to use as a denominator and cross-reference point throughout the revenue gap investigation.

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

The half-open date interval ensures all timestamps across the full calendar month are included without risk of edge cases at the month boundary.

Next step

With 610 as a confirmed total, a natural next query would be to split that count by order status to see how many of those orders were delivered versus cancelled or refunded, which directly informs the revenue gap analysis.