Sérgio Torrinha

Problem

Meridian Retail (simulated retailer) needed its brand colours applied consistently before any reports were shared externally. Chidi Okafor asked me to create a custom Power BI theme named Meridian Retail, covering a primary orange, deep navy, amber accent, and three additional chart colours, plus correctly mapped sentiment colours for good, neutral, and bad states.

What I did

The theme was authored as a JSON file with the dataColors array ordered to place Meridian orange first and deep navy second, matching the brand hierarchy Chidi specified. All six palette entries were included in sequence so that multi-series charts would draw colours in the correct order. Sentiment colours were mapped explicitly: forest green for good, amber for neutral, and red for bad. Alongside the theme work, the dimCustomers Power Query table was built with deduplication via an is_duplicate filter, a surrogate key generated with ROW_NUMBER() OVER (ORDER BY email), a concatenated full name, and a calculated customer_tier field.

Outcome

Chidi received a report with a fully applied brand theme and a customer dimension table ready for modelling work in subsequent tasks.

ROW_NUMBER() OVER( ORDER BY email ) customer_sk
 ,email
 ,first_name

This fragment from the dimCustomers M code generates a compact integer surrogate key ordered by email, replacing the original long-text customer_id to reduce file weight in Power BI.

Next step

A useful next step would be to test how the sentiment colour mapping renders across different visual types, particularly for KPI cards and conditional formatting rules, since those can override theme colours independently.

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim flagged a visible gap between gross and net revenue and needed the difference broken down clearly by order status. The specific ask was to see gross total, net total excluding cancelled, returned, and refunded orders, and the resulting gap, all in a single readable output.

What I did

A single query using GROUP BY ROLLUP(o.status) produced both the per-status rows and a Grand Total row without needing a UNION ALL. The CASE WHEN GROUPING(o.status) = 1 expression labelled the summary row cleanly. Conditional aggregation inside SUM computed net revenue by zeroing out excluded statuses, and the gap was derived as gross minus net within the same SELECT. Date filtering used the canonical >= '2023-10-01' AND < '2023-11-01' boundary to avoid implicit cast issues, and all cent values were divided by 100.0 throughout for consistent naira output.

Outcome

Nura received a single query result showing exactly where the NGN 18.4M discrepancy originated, with per-status figures she could take directly into the finance review.

SELECT
 CASE
 WHEN GROUPING(o.status) = 1 THEN 'Grand Total'
 ELSE o.status::text
 END AS order_status_or_grand_total
 ,ROUND(SUM(o.total_cents) / 100.0, 2) AS total_revenue_naira
 ,ROUND(
 SUM(
 CASE
 WHEN o.status IN ('cancelled', 'returned', 'refunded') THEN 0
 ELSE o.total_cents
 END
 ) / 100.0
 ,2
 ) AS total_net_revenue_naira

The ROLLUP on status combined with the GROUPING() guard produces both per-status rows and a Grand Total in one pass, while the nested CASE WHEN inside SUM computes net revenue without a separate subquery.

Next step

Splitting the cancelled category further by cancellation reason, if that column exists, would show whether the gap is driven by customer-initiated cancellations or operational failures, which points to different remedies for Nura.

Problem

FlowRoute Logistics (simulated logistics company) Head of Customer Experience Funmi Alade needed a defensible, per-hub performance picture for a board discussion. The brief had three parts: pull delivery rate per hub from the existing view, compute complaints per 1,000 shipments by joining complaints through shipments to destination hubs, and state honestly whether the data singles out one problem hub or shows a systemic pattern.

What I did

Part one consumed the pre-built logistics.v_hub_performance view with an ascending sort on delivery_rate_pct to surface the worst performers first. For part two, shipments were joined to complaints with a LEFT OUTER JOIN so hubs with zero complaints would remain in the result, then to hubs via destination_hub_id. The complaints-per-1000 rate used 1000.0 as the multiplier to prevent integer division and COUNT(DISTINCT ...) on both shipment_id and complaint_id to stay clean against any duplicate rows the join might introduce. The written verdict in the submission noted that Ibadan had the highest complaint rate but that the spread across all hubs was less than one percentage point, making a systemic diagnosis more accurate than naming a single scapegoat hub.

Outcome

Funmi received per-hub numbers she could present as defensible evidence that the complaint and delivery problems were network-wide, steering the board discussion away from a single-hub narrative.

SELECT
 hb.name destination_hub_name
 ,hb.city destination_hub_city
 ,COUNT( DISTINCT sp.shipment_id ) total_shipments
 ,COUNT( DISTINCT cp.complaint_id ) total_complaints
 ,ROUNd( ( 1000.0 * COUNT( DISTINCT cp.complaint_id ) ) / (COUNT( DISTINCT sp.shipment_id ) * 1.0 ), 2 ) nbr_complaints_per_1000_shipmts
FROM logistics.shipments sp
LEFT OUTER JOIN logistics.complaints cp
 ON cp.shipment_id = sp.shipment_id
INNER JOIN logistics.hubs hb
 ON sp.destination_hub_id = hb.hub_id
GROUP BY hb.name, hb.city
ORDER BY nbr_complaints_per_1000_shipmts DESC;

The LEFT OUTER JOIN to complaints preserves hubs with no complaints in the result, while the 1000.0 multiplier and COUNT(DISTINCT ...) pattern together produce an accurate, division-safe rate per hub.

Next step

Breaking the complaint rate down by category per hub would show whether one hub has a disproportionate share of, say, lost shipments versus late deliveries, which the uniform overall rate currently masks.

Problem

Funmi Alade at FlowRoute Logistics (simulated logistics company) needed a clean picture of complaint volume and composition for the board pack. The ask was total complaint count, a per-category breakdown with percentages sorted by frequency, and a clear statement on which category to lead with when presenting to the board.

What I did

A CTE structured the core aggregation, and GROUP BY ROLLUP(category) produced both the category rows and the Grand Total in one query pass without a UNION. Because logistics.complaints holds one row per complaint and complaint_id is the primary key, COUNT(*) was safe and appropriate rather than COUNT(DISTINCT ...). The GROUPING(category) = 1 flag was used to label the rollup row cleanly. Category labels were cleaned up with INITCAP(REPLACE(category::TEXT, '_', ' ')) to turn raw values like wrong_address into readable output, and a most_frequent_complaint_flag derived column directly answered Funmi's question about which category to lead with.

Outcome

Funmi received a board-ready breakdown showing total volume, per-category percentages, and a flagged lead category, all in a single query output formatted for direct presentation.

CASE
 WHEN GROUPING(category) = 1 THEN 'Grand Total'
 ELSE INITCAP( REPLACE(category::TEXT, '_', ' ') )
END complaint_category
,COUNT(*) total_complaints

The GROUPING() check distinguishes the ROLLUP summary row from category rows, while INITCAP and REPLACE transform underscore-separated raw values into clean, human-readable category labels.

Next step

Tracking complaint categories over time, by month or quarter, would show whether any single category is trending upward and give Funmi a forward-looking signal beyond the static total the board pack currently contains.

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim flagged a revenue discrepancy and asked for a first look at the orders table, specifically what order statuses exist and how many orders carry each one. The task was scoped as an orientation step before deeper investigation.

What I did

The primary query grouped the orders table by status, counted rows with COUNT(*), and sorted descending by count so the most common statuses appeared first. The table alias kept the query readable without adding complexity. Beyond the brief, a commented secondary query joined order_items to orders to calculate revenue totals by status, which produced a visible NGN 18.4M figure attached to cancelled orders. This was left commented with an explanation that it pointed toward a hypothesis, specifically that the discrepancy might come from a dashboard counting all statuses while the finance team excluded cancellations.

Outcome

Nura received a complete status enumeration and a documented hypothesis in the submitted code that framed the next investigation step before it was formally assigned.

SELECT 
 o.status
 ,COUNT( * ) total_orders
FROM orders o 
GROUP BY o.status
ORDER BY COUNT(*) DESC

A minimal GROUP BY with COUNT(*) and a descending sort gives an immediate frequency-ranked view of all statuses in the table, which was the exact orientation output Nura requested.

Next step

Confirming the hypothesis would require access to the sales dashboard source definition to check whether it filters on status, which was flagged in the submission as a dependency not yet available.