Weekly Sales Performance: Aggregating Revenue by Store, Region & Category

Business Context

BrightMart is a mid-sized retail chain operating 48 stores across four regions — North, South, East, and West — selling products across categories including Electronics, Apparel, Groceries, and Home & Kitchen. The company generates approximately $4.2M in weekly revenue across all locations. As BrightMart prepares for its quarterly business review, the regional sales director has requested a breakdown of weekly sales performance for the last 12 weeks, segmented by store, region, and product category. Leadership wants to identify which stores are top performers, which regions are trending upward, and which categories are driving the most revenue week over week.

The SQL Challenge

To answer this, you'll need to pull transaction-level sales data, join it with store information (to get region) and product information (to get category), then aggregate total revenue, units sold, and transaction count at the week level. The key SQL techniques involved are SUM(), COUNT(), and ROUND() for aggregations, DATE_TRUNC() or STRFTIME() for bucketing transactions into calendar weeks, and GROUP BY across multiple dimensions — week, region, store, and category. The dataset spans 12 weeks of daily transactions across all 48 stores.

Your Role and Deliverable

You are the data analyst on the Advanced Analytics team. Your task is to write a SQL query that returns a weekly summary table showing — for each combination of week, region, store, and product category — the total number of transactions, total units sold, and total revenue. The output will be handed to the BI team to populate a regional performance dashboard reviewed every Monday morning.