07_advanced_sql.sql

USE business_performance;
WITH store_sales AS (
    SELECT
        s.store_id,
        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue
    FROM sales s
    JOIN products p
        ON s.product_id = p.product_id
    GROUP BY s.store_id
)

SELECT
    st.store_name,
    ss.revenue
FROM store_sales ss
JOIN stores st
    ON ss.store_id = st.store_id
ORDER BY ss.revenue DESC;


WITH store_sales AS (
    SELECT
        s.store_id,
        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue
    FROM sales s
    JOIN products p
        ON s.product_id = p.product_id
    GROUP BY s.store_id
)

SELECT
    st.store_name,
    ss.revenue,
    RANK() OVER (
        ORDER BY ss.revenue DESC
    ) AS revenue_rank
FROM store_sales ss
JOIN stores st
    ON ss.store_id = st.store_id
ORDER BY revenue_rank;


WITH product_sales AS (
    SELECT
        p.product_id,
        p.product_name,
        p.department,

        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue

    FROM sales s

    JOIN products p
        ON s.product_id = p.product_id

    GROUP BY
        p.product_id,
        p.product_name,
        p.department
),

ranked_products AS (
    SELECT
        product_id,
        product_name,
        department,
        revenue,

        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY revenue DESC
        ) AS department_rank

    FROM product_sales
)

SELECT
    product_id,
    product_name,
    department,
    revenue,
    department_rank

FROM ranked_products

WHERE department_rank <= 3

ORDER BY
    department,
    department_rank;


WITH sale_values AS (
    SELECT
        s.sale_id,
        s.sale_date,
        s.store_id,
        s.employee_id,
        s.customer_id,
        s.product_id,

        ROUND(
            p.selling_price
            * s.quantity
            * (1 - s.discount / 100),
            2
        ) AS sale_value

    FROM sales s

    JOIN products p
        ON s.product_id = p.product_id
),

ranked_sales AS (
    SELECT
        sale_id,
        sale_date,
        store_id,
        employee_id,
        customer_id,
        product_id,
        sale_value,

        ROW_NUMBER() OVER (
            PARTITION BY store_id
            ORDER BY sale_value DESC
        ) AS sale_rank

    FROM sale_values
)

SELECT
    rs.sale_id,
    rs.sale_date,
    st.store_name,
    p.product_name,
    rs.sale_value

FROM ranked_sales rs

JOIN stores st
    ON rs.store_id = st.store_id

JOIN products p
    ON rs.product_id = p.product_id

WHERE rs.sale_rank = 1

ORDER BY rs.sale_value DESC;


WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(s.sale_date, '%Y-%m-01') AS sales_month,

        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue

    FROM sales s

    JOIN products p
        ON s.product_id = p.product_id

    GROUP BY DATE_FORMAT(s.sale_date, '%Y-%m-01')
),

monthly_growth AS (
    SELECT
        sales_month,
        revenue,

        LAG(revenue) OVER (
            ORDER BY sales_month
        ) AS previous_month_revenue

    FROM monthly_sales
)

SELECT
    sales_month,
    revenue,
    previous_month_revenue,

    ROUND(
        revenue - previous_month_revenue,
        2
    ) AS revenue_change,

    ROUND(
        (
            (revenue - previous_month_revenue)
            / previous_month_revenue
        ) * 100,
        2
    ) AS growth_percentage

FROM monthly_growth

ORDER BY sales_month;


WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(s.sale_date, '%Y-%m-01') AS sales_month,

        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue

    FROM sales s

    JOIN products p
        ON s.product_id = p.product_id

    GROUP BY DATE_FORMAT(s.sale_date, '%Y-%m-01')
)

SELECT
    sales_month,
    revenue,

    ROUND(
        SUM(revenue) OVER (
            ORDER BY sales_month
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        ),
        2
    ) AS running_total_revenue

FROM monthly_sales

ORDER BY sales_month;


WITH monthly_sales AS (
    SELECT
        YEAR(s.sale_date) AS sales_year,
        MONTH(s.sale_date) AS sales_month,

        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue

    FROM sales s

    JOIN products p
        ON s.product_id = p.product_id

    GROUP BY
        YEAR(s.sale_date),
        MONTH(s.sale_date)
)

SELECT
    sales_month,

    MAX(
        CASE
            WHEN sales_year = 2024 THEN revenue
        END
    ) AS revenue_2024,

    MAX(
        CASE
            WHEN sales_year = 2025 THEN revenue
        END
    ) AS revenue_2025,

    ROUND(
        (
            MAX(
                CASE
                    WHEN sales_year = 2025 THEN revenue
                END
            )
            -
            MAX(
                CASE
                    WHEN sales_year = 2024 THEN revenue
                END
            )
        )
        /
        MAX(
            CASE
                WHEN sales_year = 2024 THEN revenue
            END
        ) * 100,
        2
    ) AS yoy_growth_percentage

FROM monthly_sales

GROUP BY sales_month

ORDER BY sales_month;


WITH employee_sales AS (
    SELECT
        e.employee_id,
        e.employee_name,
        st.store_name,

        ROUND(
            SUM(
                p.selling_price
                * s.quantity
                * (1 - s.discount / 100)
            ),
            2
        ) AS revenue

    FROM sales s

    JOIN employees e
        ON s.employee_id = e.employee_id

    JOIN stores st
        ON e.store_id = st.store_id

    JOIN products p
        ON s.product_id = p.product_id

    GROUP BY
        e.employee_id,
        e.employee_name,
        st.store_name
),

ranked_employees AS (
    SELECT
        employee_id,
        employee_name,
        store_name,
        revenue,

        RANK() OVER (
            ORDER BY revenue DESC
        ) AS employee_rank

    FROM employee_sales
)

SELECT
    employee_id,
    employee_name,
    store_name,
    revenue,
    employee_rank

FROM ranked_employees

WHERE employee_rank <= 5

ORDER BY employee_rank;