Aliyu Usman

Problem

Meridian Retail (simulated retailer) was investigating a revenue gap in its October 2023 numbers, and the senior analytics team needed one verified fact before proceeding. The question put to me was simple: how many orders were placed in October 2023, in total, using the created_at column of the orders table?

What I did

Rather than using BETWEEN, which can behave unexpectedly with datetime values, I wrote the range as created_at >= '2023-10-01' AND created_at < '2023-11-01'. That upper boundary safely captures every timestamp through the last second of October 31st without excluding any rows. COUNT(*) was the right aggregate here because the task asks for a total row count, not a count of a specific non-null column. The query targets retail.orders as specified and returns a single, clearly named column.

Outcome

The senior analysts had a single verified order count to use as a reference point when reconciling the October revenue figures.

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

The open upper boundary on 2023-11-01 ensures every timestamp within October, including 23:59:59 on the 31st, is included without truncation.

Next step

A useful next step would be to split that 610 figure by order status to see how many of those October orders ended as cancelled, returned, or refunded, which would speak directly to the revenue gap.

Problem

Before any analysis could begin on the Meridian Retail (simulated retailer) dataset, the team needed a clear picture of what the file contained and where the data could not be trusted. The task was to browse Meridian_Data.xlsx, document the workbook structure, and flag at least three quality issues with evidence on a dedicated audit sheet.

What I did

I built a Data Audit sheet that opened with a workbook overview: six sheets, 1,849 order rows, 16 columns, and a Q4 2023 date range from 2023-10-01 to 2023-12-31. From there I enumerated all seven distinct order statuses with their row counts. Five issues were then documented, each with a sheet reference, an evidence count, a plain-language explanation of the business impact, and a concrete suggested action. The most consequential finding was the dual-format category column in Order_Items, where values like 'Women's Clothing' and 'womens-clothing' coexist across 24 distinct values, a problem that would silently break any group-by analysis on category. Two further issues, 66 rows of negative stock_qty and a duplicate product name sharing different SKUs, were flagged alongside the fully blank legacy_order_ref column.

Outcome

The audit sheet gave a data engineering team a ready-to-act document: each issue named, evidenced, and accompanied by a specific remediation step, with a recommendation to join on SKU or product_id rather than product name to avoid the duplicate-name problem.

Next step

The audit notes the category inconsistency but does not quantify how many rows would be misclassified under each format variant, running a frequency breakdown of raw category values before and after standardisation would show the full scope of that problem.

Problem

A Meridian Retail (simulated retailer) stakeholder needed the verified clean customer count delivered by email before walking into an investor meeting. The brief called for one sentence on why that number was the right one to use, and one sentence flagging the duplicate records so no one would be caught off guard.

What I did

To produce the number, I queried the customers table using COUNT(*) filtered on is_duplicate = FALSE, which isolates only the rows the business considers valid records. The result was aliased as clean_customer_count to make the output unambiguous. The submission included the working SQL and the result of 2,131, but the formatted email itself was not included in what I handed in.

Outcome

The query confirmed the correct number for the stakeholder, though the missing email meant the deliverable was incomplete and the communication task was not fulfilled.

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

Filtering on is_duplicate = FALSE before counting ensures the result excludes flagged duplicate rows, making the count a reliable baseline.

Next step

It would be worth running a breakdown of the duplicate rows by sign-up date or acquisition channel to understand whether duplicates are concentrated in a particular onboarding period, which could point to where the data entry problem originated.

Problem

Multiple teams at Meridian Retail (simulated retailer) were reporting different customer counts, and the senior analysts were investigating why. My piece of the reconciliation was to produce one authoritative clean count using the is_duplicate flag in the customers table, giving the rest of the team a single number to measure every other count against.

What I did

The query filters to is_duplicate = FALSE, which excludes rows the data team had already marked as bad records, then applies COUNT(*) to count every remaining row. Aliasing the output as clean_customer_count means anyone reading the result set immediately knows what the figure represents without needing to trace back through the query. Keeping the query to these three elements, a filter, an aggregate, and a clear alias, made it straightforward to explain and audit.

Outcome

The reconciliation team had an unambiguous, reproducible number to anchor their work, produced by a query short enough to inspect and rerun in seconds.

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

The alias clean_customer_count makes the single-row result self-labelling, removing any ambiguity when the output is shared or pasted into a report.

Next step

The next useful step would be to count how many rows have is_duplicate = TRUE and inspect whether those duplicates share email addresses, phone numbers, or registration dates, to understand what matching rule generated the flag.

Problem

Meridian Retail's (simulated retailer) CFO, Nura Ibrahim, flagged a revenue discrepancy in the October 2023 figures and asked me to profile the orders table before the investigation deepened. The question was what order statuses existed and how many orders carried each status, scoped to the period under review.

What I did

Scoping the query to October 2023 with a date filter kept the results focused on the CFO's period of concern. Beyond the status count the brief asked for, I added SUM(total_cents) / 100.0 AS total_revenue to surface revenue by status in naira, dividing by 100.0 rather than 100 to preserve decimal precision. Grouping by status and ordering by total_revenue descending meant the most financially significant statuses appeared at the top of the result set. That structure made it immediately visible which statuses were contributing to the gap, without any additional formatting.

Outcome

The result set gave the CFO a single query output connecting order volume and revenue loss by status, letting the investigation move straight to the most financially significant categories.

SELECT 
 status,
 COUNT(order_id) AS order_count,
 SUM(total_cents) / 100.0 AS total_revenue
FROM 
 retail.orders
WHERE 
 created_at >= '2023-10-01'
 AND created_at <= '2023-10-31'
GROUP BY 
 status
ORDER BY 
 total_revenue DESC;

Dividing total_cents by 100.0 converts the stored integer pence values to naira with decimal precision, and ordering by total_revenue descending surfaces the highest-value statuses at the top of the output.

Next step

The upper boundary used here, <= '2023-10-31', cuts off at midnight on October 30th and misses some late-day transactions on the 31st; replacing it with < '2023-11-01' would capture the full month and close the small gap between the 589 rows returned here and the 610 confirmed in the order-count task.