SQL
Using CTEs for Multi-Step Data Analysis
Improve query readability and structure complex SQL logic using Common Table Expressions (CTEs) to first calculate daily sales and then find monthly averages.
WITH DailySales AS (
SELECT
CAST(order_date AS DATE) AS sale_day, -- Adjust DATE extraction for your DB (e.g., DATE_TRUNC, DATE_FORMAT)
SUM(total_amount) AS daily_revenue
FROM
orders
GROUP BY
CAST(order_date AS DATE)
),
MonthlySales AS (
SELECT
EXTRACT(YEAR FROM sale_day) AS year,
EXTRACT(MONTH FROM sale_day) AS month,
SUM(daily_revenue) AS monthly_revenue,
COUNT(sale_day) AS days_with_sales
FROM
DailySales
GROUP BY
EXTRACT(YEAR FROM sale_day),
EXTRACT(MONTH FROM sale_day)
)
SELECT
year,
month,
monthly_revenue,
(monthly_revenue / days_with_sales) AS average_daily_revenue_in_month
FROM
MonthlySales
ORDER BY
year, month;
How it works: This query uses two Common Table Expressions (CTEs). `DailySales` calculates the total revenue for each day. `MonthlySales` then uses the `DailySales` results to aggregate daily revenues into monthly totals and count the number of days with sales within each month. Finally, the main query calculates the average daily revenue per month, making complex calculations more readable and modular.