SQL Practice Problems - Day 6
Focus: CTEs and Subqueries

1 - Calculate average product weight using a subquery.
Input Table: Products
Output: Avg_Weight

2 - List products heavier than average using a CTE.
Input Table: Products
Output: SKU_Code, Design_No, Weight

3 - Find customers who spent more than average using a subquery.
Input Tables: Customers, Sales_Transactions
Output: Customer_ID, Customer_Name, Total_Spent

4 - Show monthly sales totals using a CTE.
Input Table: Sales_Transactions
Output: Month, Total_Sales

5 - Identify products with above-average inventory values using a subquery.
Input Tables: Inventory, Pricing
Output: SKU_Code, Inventory_Value

6 - Rank customers by total spending using a CTE.
Input Tables: Customers, Sales_Transactions
Output: Customer_ID, Customer_Name, Total_Spent, Rank

7 - Find products sold in all months using correlated subquery.
Input Tables: Products, Sales_Transactions
Output: SKU_Code, Design_No

8 - Calculate running total of sales using CTE with window function.
Input Table: Sales_Transactions
Output: Sale_ID, Date, Gross_Amount, Running_Total

9 - Show products with no sales using NOT EXISTS.
Input Tables: Products, Sales_Transactions
Output: SKU_Code, Design_No

10 - Display hierarchical product categories using recursive CTE.
Input Table: Products
Output: Category, Level

11 - Find customers who purchased all product categories using correlated subqueries.
Input Tables: Customers, Sales_Transactions, Products
Output: Customer_ID, Customer_Name

12 - Calculate percentage of total sales for each product using CTEs.
Input Tables: Products, Sales_Transactions
Output: SKU_Code, Design_No, Pct_Total_Sales

13 - Identify products with consistent pricing across platforms using CTE with CASE.
Input Table: Pricing
Output: SKU_Code, Is_Consistent

14 - Show customers with above-average purchases in each category using multiple CTEs.
Input Tables: Customers, Sales_Transactions, Products
Output: Customer_ID, Customer_Name, Category, Total_Spent

15 - Find products that are always sold together using self-join subquery.
Input Table: Sales_Transactions
Output: Product_Pair, Frequency