DQL - Advanced Analytics

Common Table Expressions (CTEs), defined using the WITH keyword, are temporary result sets. In retail analytics, we often have "multi-step" logic. For example: First calculate total tax per region, THEN calculate the average tax across all regions.

CTEs act like "variables" for your queries, making them much easier to read and maintain than deeply nested subqueries. They are the standard for professional SQL development in global firms.

WITH RegionalAverages AS (    SELECT region, AVG(total_sales) as avg_regional_sales    FROM store_performance    GROUP BY region)SELECT     s.store_name,     s.region,     s.total_sales,    r.avg_regional_sales,    (s.total_sales - r.avg_regional_sales) as varianceFROM store_performance sJOIN RegionalAverages r ON s.region = r.regionORDER BY variance ASC;

Traditional GROUP BY collapses your data into a single row. But what if you want to see the individual transaction AND the total sales for that day side-by-side? That's where Window Functions shine.

Using the OVER() clause, we can perform calculations across a "window" of rows. This is essential for:

• Running Totals: Seeing how revenue grows day-by-day.
• Ranking: Finding the #1 salesperson in every branch.
• Lead/Lag: Comparing this month's stock to last month's immediately.

SELECT     sale_date,     amount,    SUM(amount) OVER (ORDER BY sale_date) as running_totalFROM sales_table;

Aggregations like SUM, AVG, and COUNT are the bread and butter of retail reports. However, power users leverage the HAVING clause to filter grouped data. While WHERE filters individual rows *before* they are grouped, HAVING filters the results *after* the math is done.

Example: Find only the warehouses that have more than 50,000 items in stock.

SELECT warehouse_id, SUM(stock_count) as totalFROM inventoryGROUP BY warehouse_idHAVING SUM(stock_count) > 50000;