Retail Sales Analysis with SQL

A SQL portfolio project analyzing retail sales performance, promotion effectiveness, and customer behavior using a retail transactions dataset (2009–2012).

Originally published on Rose Data Scientist.

Overview

This project analyzes ~5,500 raw transaction records from a retail store (dqlab_sales_store), covering order status (finished, returned, cancelled), sales, discounts, and product categorization from 2009 to 2012.

Management wanted answers to the following business questions:

1. Overall performance — total orders and sales revenue by year (2009–2012)
2. Category performance — sales by product sub-category, comparing 2011 vs. 2012
3. Promotion efficiency — burn rate (discount spend vs. sales) by year, against a 4.5% target ceiling
4. Promotion efficiency by category — burn rate broken out by product category/sub-category
5. Customer activity — number of distinct customers transacting per year

Dataset

Column	Description
OrderID	Unique order identifier
Order Status	Finished / Returned / Cancelled
Customer	Customer identifier
Order Date	Date of the order
Order Quantity	Units ordered
Sales	Sales value
Discount %	Discount percentage applied
Discount	Discount value
Product Category	High-level product category
Product Sub-Category	Detailed product category

Table name used throughout the queries: dqlab_sales_store

Queries

File	Business Question
01_overall_performance_by_year.sql	Total sales & order count by year (finished orders only)
02_performance_by_subcategory.sql	Sales by product sub-category, 2011 vs 2012
03_promotion_burn_rate_by_year.sql	Burn rate = (total discount / total sales) × 100, by year
04_promotion_burn_rate_by_subcategory.sql	Burn rate by category/sub-category, 2012
05_customers_per_year.sql	Distinct customers transacting per year

Key Concepts Used

• Aggregate functions (SUM, COUNT, COUNT DISTINCT)
• GROUP BY / ORDER BY
• Date extraction (YEAR(), EXTRACT(YEAR FROM ...))
• Filtering with WHERE on order status and date ranges
• Derived metrics (burn rate calculation)

Notes

• All queries filter to order_status = 'Order Finished' unless otherwise noted, to exclude returned/cancelled orders from revenue figures.
• The promotion burn rate target ceiling defined by the business is 4.5%.

License

MIT