WITH recent_orders AS (
    SELECT
        customer_id,
        order_id,
        order_date,
        total_amount
    FROM orders
    WHERE order_date >= DATEADD(day, -30, GETDATE())
)
SELECT
    customer_id,
    COUNT(order_id)   AS order_count,
    SUM(total_amount) AS total_spent
FROM recent_orders
GROUP BY customer_id
ORDER BY total_spent DESC;

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: top-level rows (no manager)
    SELECT
        employee_id,
        manager_id,
        employee_name,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: join children to their parent's result
    SELECT
        e.employee_id,
        e.manager_id,
        e.employee_name,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
        ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level, employee_name;
-- Note: SQL Server / Oracle: drop the RECURSIVE keyword (just WITH employee_hierarchy AS (...))

SELECT
    o.order_id,
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN order_items oi
    ON o.order_id = oi.order_id
LEFT JOIN products p
    ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC;

SELECT
    DATE_TRUNC('month', order_date) AS order_month,     -- PostgreSQL
    -- FORMAT(order_date, 'yyyy-MM')  AS order_month,    -- SQL Server
    -- DATE_FORMAT(order_date, '%Y-%m') AS order_month,  -- MySQL
    COUNT(*)          AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS order_rank,
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE = NULL,
    @EndDate    DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        order_id,
        order_date,
        total_amount
    FROM orders
    WHERE customer_id = @CustomerId
        AND (@StartDate IS NULL OR order_date >= @StartDate)
        AND (@EndDate   IS NULL OR order_date <= @EndDate)
    ORDER BY order_date DESC;
END;
-- Call it: EXEC GetCustomerOrders @CustomerId = 101, @StartDate = '2026-01-01';

MERGE INTO customers AS target
USING staging_customers AS source
    ON target.customer_id = source.customer_id
WHEN MATCHED THEN
    UPDATE SET
        target.customer_name = source.customer_name,
        target.email         = source.email,
        target.updated_at    = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (customer_id, customer_name, email, created_at)
    VALUES (source.customer_id, source.customer_name, source.email, GETDATE());