SQL portfolio project based on Case Study #7 from the 8 Week SQL Challenge. This project analyzes transactional sales data from Balanced Tree Clothing Co., a fictional retail fashion company, to generate business insights using SQL. The goal of this project is to demonstrate practical SQL skills including:
- relational data modeling
- multi-table joins
- aggregation and analytical queries
- hierarchical product analysis
- business reporting
Balanced Tree Clothing Co. wants to better understand its sales performance, product performance, and transaction behavior. Using multiple relational datasets, this project explores:
- overall sales metrics
- transaction patterns
- product and category performance
- segment level revenue contribution
- product penetration and bundling opportunities
This project uses 4 relational tables.
Contains transaction-level sales data.
- prod_id: product identifier
- qty: quantity purchased
- price: product price
- discount: discount percentage applied
- member: whether customer is a member
- txn_id: transaction identifier
- start_txn_time: timestamp of transaction
Provides enriched product information including hierarchy mapping.
- product_id: product identifier
- price: base price
- product_name: name of the product
- category_id: category identifier
- segment_id: segment identifier
- style_id: style identifier
- category_name: category name
- segment_name: segment name
- style_name: style name
Defines the hierarchical structure of products.
- id: hierarchy identifier
- parent_id: parent hierarchy id
- level_text: hierarchy level
- level_name: hierarchy description
Contains pricing information for products.
- product_id: product identifier
- price: product price
This project answers several business questions using SQL.
- What is the total quantity sold across all transactions?
- What is the gross revenue before discount?
- What is the total discount value given to customers?
- What is the net revenue after discounts?
Example calculation: net_revenue = qty × price × (1 - discount)
- How many unique transactions occurred?
- What is the average number of products per transaction?
- What is the average transaction value?
- What percentage of transactions come from members vs non-members?
- What is the average discount per transaction?
Using joins between sales and product_details.
- Which products generate the highest revenue?
- What are the top 3 products by revenue?
- Which segments contribute the most revenue?
- What is the revenue contribution by category?
Using product_hierarchy and product_details.
- Which category generates the highest sales revenue?
- What are the top selling products within each segment?
- What is the revenue share of each category?
This analysis demonstrates the use of hierarchical product structures.
Product penetration measures how frequently a product appears in transactions.
- What percentage of transactions include each product?
- Which products appear most frequently in customer purchases?
Identify products that are frequently purchased together.
- What are the most common product combinations in a single transaction?
- Which 3-product combinations occur most frequently?
This analysis helps identify cross-selling opportunities.
To simulate a real-world business reporting workflow, a reusable monthly sales report was developed using transaction-level sales data.
The report provides:
- Executive sales summary
- Category performance analysis
- Segment performance analysis
- Product performance ranking
- Customer purchasing behavior metrics
This exercise focuses on transforming raw transactional data into business-oriented reporting outputs that could be consumed by stakeholders and management teams.
This project demonstrates practical SQL skills used in real-world data analysis:
- Relational data modeling
- Multi-table joins
- Aggregation and grouping
- Common Table Expressions (CTEs)
- Window functions
- Ranking and contribution analysis
- Product penetration analysis
- Product bundling analysis
- Hierarchical data analysis
- Analytical SQL problem solving
- SQL (PostgreSQL / MySQL compatible)
- GitHub
Case Study #7 from the 8 Week SQL Challenge by Danny Ma.
Aziz Dharmawan Apray
SQL Portfolio Project