Hossana Bamidele

Problem

Meridian Retail (simulated retailer) had different teams reporting different customer counts, creating uncertainty about which figure to use as a baseline. The seniors were investigating the root cause, and my piece of the work was to produce the single authoritative clean count. The question was: how many customer records are legitimate once duplicates are excluded?

What I did

Filtering on the boolean is_duplicate column was the direct route, so I wrote a WHERE clause using is_duplicate = FALSE to isolate only valid rows. From that filtered set, COUNT(*) captures every qualifying row regardless of null values in other columns, which is the right aggregation when the goal is a row count rather than a field-level count. Aliasing the result as clean_customer_count meant the output column name answered the question without requiring further explanation.

Outcome

Meridian now has a single agreed-upon number to anchor customer-level reporting, removing the ambiguity between team figures.

SELECT COUNT(*) AS clean_customer_count
FROM customers
WHERE is_duplicate = FALSE;

This query filters to non-duplicate rows and counts them with a self-documenting alias, producing the single baseline figure the team needed.

Next step

A useful next step would be to break the 2,131 clean customers down by acquisition channel or registration date, to understand whether the duplicate problem is concentrated in a particular cohort.

Problem

Meridian Retail (simulated retailer) needed a single at-a-glance gross revenue figure while a broader revenue question was being investigated. The amounts in the orders table are stored in kobo, not naira, so a direct sum would overstate the figure by a factor of one hundred. The task was to connect Power BI to the Meridian database, handle the unit conversion, and display total gross revenue on a clearly titled card visual.

What I did

I loaded the retail orders table from the Meridian database into Power BI and created a calculated column called TOTAL_NAIRA using the formula 'retail orders'[total_cents]/100, dividing each row's kobo value by 100 to express it in naira before aggregation. Placing this column on a card visual and summing it gave a gross revenue figure of 2.31bn naira. The card title was set to Total Gross Revenue so the visual is self-labelling at a glance.

Outcome

The stakeholder has a single, clearly labelled revenue card they can reference immediately while the deeper investigation continues.

Next step

The calculated column approach converts at the row level before summing, so a natural next step would be to test whether a measure using SUMX produces an identical result and to filter the card to a specific date range for period comparisons.

Problem

Meridian Retail (simulated retailer) had a reported gross revenue figure of approximately 56.9 million naira for October 2023, and the task was to verify whether the data file agreed with that claim. Rather than writing a formula from scratch, I was asked to confirm the number manually using the orders sheet in the provided workbook. The specific question was whether the file-level total matched the sales system total.

What I did

Building a PivotTable sourced from Orders!A1:P1850 gave me a structured month-level summary without manually summing each row. Rows were grouped by month and order date, and the values area summed the total_naira column, which is already stored in naira in this file. Adding order_id to the Filters pane kept the layout clean and gave me a filter handle if I needed to isolate specific records. Checking that the Grand Total equalled the October row confirmed that only October data was present in the dataset.

Outcome

The reconciliation confirmed the file and the sales system agree on October revenue, giving the senior analysts a verified starting point for their gap investigation.

Next step

Because the dataset contains only October, a meaningful next step would be to bring in a prior month's file and build a comparable PivotTable to check whether the revenue gap the seniors identified is visible as a month-on-month movement.

Problem

Meridian Retail (simulated retailer) had senior analysts investigating a revenue discrepancy in October 2023 data. Before deeper analysis could proceed, the team needed one confirmed fact: how many orders were taken in that month? The CFO's team needed a single, verified order count to use as a reference point.

What I did

Using a half-open date interval, created_at >= '2023-10-01' AND created_at < '2023-11-01', correctly captures every order timestamped within October without risk of including any November 1st records that a BETWEEN or less-than-or-equal approach might pull in. COUNT(*) with the alias total_orders_oct_2023 produces a result column that is readable without additional documentation. The query is intentionally minimal: no joins, no subqueries, just a clean filter and count.

Outcome

The senior analysts had a confirmed order count to anchor their revenue gap work, removing one potential variable from the investigation.

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

The half-open interval on created_at is the key detail here: it avoids the off-by-one error that a less-than-or-equal boundary would introduce at midnight on November 1st.

Next step

Knowing the total is 610, a useful next step would be to compare that count against the same period in a prior month to determine whether October's volume was unusually high or low relative to trend.

Problem

Meridian Retail (simulated retailer) CFO Nura Ibrahim flagged a revenue discrepancy and needed to understand the composition of October 2023 orders before narrowing the investigation. The specific question was: what order statuses exist, how many orders carry each one, and what revenue is associated with each group?

What I did

Grouping by the status column and applying COUNT(*) per group answered the distribution question directly. Adding ROUND(SUM(total_cents) / 100.0, 2) as gross_revenue_naira converted kobo to naira at the aggregation step and rounded to two decimal places, avoiding floating-point noise in the output. Ordering by gross_revenue_naira descending puts the highest-revenue statuses at the top, which is the natural reading order for a CFO scanning for where value is concentrated. The same boundary-safe date filter from the order-count task, created_at >= '2023-10-01' AND created_at < '2023-11-01', was applied consistently to keep results scoped to October.

Outcome

The CFO received a single query result showing every status, its order volume, and its naira revenue contribution, giving her the distribution view needed to decide which statuses to investigate further.

SELECT 
 status,
 COUNT(*) AS order_count,
 ROUND(SUM(total_cents) / 100.0, 2) AS gross_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 SUM with division by 100.0 and ROUND converts kobo to naira at the group level, and ORDER BY gross_revenue_naira DESC surfaces the most financially significant statuses first.

Next step

Cancelled, returned, and refunded orders each represent revenue that did not settle, so the logical next step is to quantify the combined value of those three statuses and compare it against the reported discrepancy amount.