Retail · Excel
Junior data analyst working retail and operations problems in SQL: aggregation, revenue reconciliation, and segmentation
Scores reflect independent, automated grading of simulated business tasks completed on DataSimHQ.
Working through retail and operations scenarios on a graded simulation platform, I have built SQL queries that answer concrete business questions: order counts, revenue reconciliation, date-range filtering, and customer acquisition breakdowns. Each task came with a stakeholder brief, a real dataset, and an independent reviewer who checked both the code and the findings.
My strongest work so far is in revenue analysis, where I decomposed gross and net revenue by order status to reconcile a board-level discrepancy, and in customer segmentation, where I excluded duplicates to produce a clean acquisition-channel ranking. I am building the habit of writing findings that are decision-ready, not just technically correct.
SQL Aggregation
Query Design Patterns
SQL Filtering and Logic
Retail · Excel
Retail · Excel
Retail · SQL
Independent grading: 100/100(verify, opens in a new tab)
Meridian Retail (simulated retailer) was investigating a revenue gap in its October 2023 numbers. Before senior analysts could begin filtering and segmenting, they needed one verified baseline: the total number of orders placed in October 2023. The question came from Nura Ibrahim, who needed the count confirmed against the orders table's created_at column.
To avoid boundary errors common in date filtering, I used a half-open interval: created_at >= '2023-10-01' AND created_at < '2023-11-01'. This captures every order in the month without risk of missing the last day or double-counting midnight timestamps. COUNT(*) was the right aggregate because it counts every row regardless of NULLs, giving a reliable total. The result column was aliased as october_orders so the output is immediately readable without referring back to the query.
October 2023 contained 610 orders in total, confirmed against the orders table's created_at column.
The verified count gave the senior analysts a correct baseline from which to begin their revenue gap investigation.
SELECT COUNT(*) AS october_orders FROM orders WHERE created_at >= '2023-10-01' AND created_at < '2023-11-01';
This query uses a boundary-safe half-open interval to capture every October order and returns the total as a labelled column.
A useful follow-on would be to break the 610 orders down by status immediately, to see whether the distribution of delivered, cancelled, and returned orders in October looks unusual compared to other months.
Retail · SQL
Independent grading: 95/100(verify, opens in a new tab)
Meridian Retail (simulated retailer) had a flagged revenue discrepancy under investigation by CFO Nura Ibrahim. Before any filtering or segmentation could begin, the task was to establish what order statuses existed in the orders table and how many orders carried each one.
Grouping by status with COUNT(*) aliased as order_count is the minimal, correct structure for this kind of table profile. Ordering by order_count descending makes the dominant statuses visible immediately. No joins or subqueries were needed; the orders table alone held everything required, and the search path was already set to the retail schema so no prefix was necessary.
Seven distinct statuses exist in the orders table, with delivered accounting for 14,061 orders, roughly 59 percent of all records, and the remaining six statuses covering the balance.
The status breakdown gave the investigation team a clear picture of how orders were distributed, which is a necessary reference point before any revenue filtering is applied.
SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY order_count DESC;
This query profiles all distinct order statuses and their frequencies in a single pass, ordered so the most common status appears first.
Knowing that delivered dominates at roughly 59 percent, a natural next question is whether that ratio holds across all time periods or whether specific months show an unusually high share of cancelled or returned orders.