Window Functions Practice

SELECT
    c.id             AS customer_id,
    c.name           AS customer_name,
    o.order_number,
    o.ordered_at,
    o.total_amount,
    ROW_NUMBER() OVER (
        PARTITION BY c.id
        ORDER BY o.ordered_at
    ) AS order_seq
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
ORDER BY c.id, order_seq
LIMIT 20;

WITH monthly_revenue AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        ROUND(SUM(total_amount), 0) AS revenue
    FROM orders
    WHERE ordered_at LIKE '2024%'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
SELECT
    year_month,
    revenue,
    RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM monthly_revenue
ORDER BY year_month;

SELECT
    p.name            AS product_name,
    cat.name          AS category,
    ROUND(SUM(oi.quantity * oi.unit_price), 0) AS product_revenue,
    ROUND(SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY p.category_id
    ), 0) AS category_revenue,
    ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
        / SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY p.category_id),
    1) AS pct_of_category
FROM order_items AS oi
INNER JOIN orders     AS o   ON oi.order_id   = o.id
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE o.ordered_at LIKE '2024%'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY p.id, p.name, cat.name, p.category_id
ORDER BY product_revenue DESC
LIMIT 15;

SELECT
    c.id            AS customer_id,
    c.name          AS customer_name,
    o.ordered_at,
    o.total_amount,
    SUM(o.total_amount) OVER (
        PARTITION BY c.id
        ORDER BY o.ordered_at
        ROWS UNBOUNDED PRECEDING
    ) AS running_total
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE c.id BETWEEN 1 AND 5
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
ORDER BY c.id, o.ordered_at;

WITH method_stats AS (
    SELECT
        method,
        COUNT(*)              AS payment_count,
        ROUND(SUM(amount), 0) AS total_amount
    FROM payments
    WHERE status = 'completed'
      AND paid_at LIKE '2024%'
    GROUP BY method
)
SELECT
    method,
    payment_count,
    total_amount,
    NTILE(4) OVER (ORDER BY payment_count DESC) AS quartile
FROM method_stats
ORDER BY payment_count DESC;

WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        ROUND(SUM(total_amount), 0) AS revenue
    FROM orders
    WHERE ordered_at >= '2023-01-01'
      AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
SELECT
    year_month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY year_month) AS prev_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY year_month) AS diff,
    ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY year_month))
        / LAG(revenue, 1) OVER (ORDER BY year_month), 1) AS growth_pct
FROM monthly
ORDER BY year_month;

WITH order_gaps AS (
    SELECT
        o.customer_id,
        c.name,
        o.ordered_at,
        LEAD(o.ordered_at, 1) OVER (
            PARTITION BY o.customer_id
            ORDER BY o.ordered_at
        ) AS next_order_at,
        ROUND(
            JULIANDAY(LEAD(o.ordered_at, 1) OVER (
                PARTITION BY o.customer_id
                ORDER BY o.ordered_at
            )) - JULIANDAY(o.ordered_at),
        0) AS days_to_next
    FROM orders AS o
    INNER JOIN customers AS c ON o.customer_id = c.id
    WHERE c.grade = 'VIP'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
)
SELECT
    customer_id,
    name,
    COUNT(days_to_next)       AS gap_count,
    ROUND(AVG(days_to_next), 1) AS avg_days_between,
    MIN(days_to_next)           AS min_gap,
    MAX(days_to_next)           AS max_gap
FROM order_gaps
WHERE days_to_next IS NOT NULL
GROUP BY customer_id, name
ORDER BY avg_days_between ASC
LIMIT 10;

WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        ROUND(SUM(total_amount), 0) AS revenue
    FROM orders
    WHERE ordered_at >= '2023-01-01'
      AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
SELECT
    year_month,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY year_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 0) AS moving_avg_3m,
    COUNT(*) OVER (
        ORDER BY year_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS window_size
FROM monthly
ORDER BY year_month;

WITH ranked AS (
    SELECT DISTINCT
        cat.name AS category,
        FIRST_VALUE(p.name) OVER (
            PARTITION BY p.category_id
            ORDER BY p.price DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS most_expensive,
        FIRST_VALUE(p.price) OVER (
            PARTITION BY p.category_id
            ORDER BY p.price DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS max_price,
        LAST_VALUE(p.name) OVER (
            PARTITION BY p.category_id
            ORDER BY p.price DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS cheapest,
        LAST_VALUE(p.price) OVER (
            PARTITION BY p.category_id
            ORDER BY p.price DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS min_price
    FROM products AS p
    INNER JOIN categories AS cat ON p.category_id = cat.id
    WHERE p.is_active = 1
)
SELECT DISTINCT
    category,
    most_expensive,
    max_price,
    cheapest,
    min_price
FROM ranked
ORDER BY category;

WITH product_ratings AS (
    SELECT
        p.id,
        p.name,
        ROUND(AVG(r.rating), 2) AS avg_rating,
        COUNT(*)                AS review_count
    FROM reviews AS r
    INNER JOIN products AS p ON r.product_id = p.id
    GROUP BY p.id, p.name
    HAVING COUNT(*) >= 3
),
tiered AS (
    SELECT
        *,
        NTILE(5) OVER (ORDER BY avg_rating) AS tier
    FROM product_ratings
)
SELECT
    tier,
    COUNT(*)                     AS product_count,
    ROUND(MIN(avg_rating), 2)    AS min_rating,
    ROUND(MAX(avg_rating), 2)    AS max_rating,
    ROUND(AVG(avg_rating), 2)    AS avg_of_avg,
    ROUND(AVG(review_count), 1)  AS avg_reviews
FROM tiered
GROUP BY tier
ORDER BY tier;

WITH customer_totals AS (
    SELECT
        c.id,
        c.name,
        c.grade,
        COUNT(*)                    AS order_count,
        ROUND(SUM(o.total_amount), 0) AS total_spent
    FROM customers AS c
    INNER JOIN orders AS o ON c.id = o.customer_id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY c.id, c.name, c.grade
),
ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY grade
            ORDER BY total_spent DESC
        ) AS rn
    FROM customer_totals
)
SELECT
    grade,
    rn   AS rank_in_grade,
    name AS customer_name,
    order_count,
    total_spent
FROM ranked
WHERE rn <= 3
ORDER BY
    CASE grade
        WHEN 'VIP' THEN 1
        WHEN 'GOLD' THEN 2
        WHEN 'SILVER' THEN 3
        WHEN 'BRONZE' THEN 4
    END,
    rn;

WITH product_monthly AS (
    SELECT
        p.id       AS product_id,
        p.name     AS product_name,
        SUBSTR(o.ordered_at, 1, 7) AS year_month,
        ROUND(SUM(oi.quantity * oi.unit_price), 0) AS monthly_revenue
    FROM order_items AS oi
    INNER JOIN orders   AS o ON oi.order_id   = o.id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE o.ordered_at LIKE '2024%'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY p.id, p.name, SUBSTR(o.ordered_at, 1, 7)
),
top5 AS (
    SELECT product_id
    FROM product_monthly
    GROUP BY product_id
    ORDER BY SUM(monthly_revenue) DESC
    LIMIT 5
)
SELECT
    pm.product_name,
    pm.year_month,
    pm.monthly_revenue,
    ROUND(AVG(pm.monthly_revenue) OVER (
        PARTITION BY pm.product_id
    ), 0) AS avg_monthly,
    ROUND(100.0 * pm.monthly_revenue
        / AVG(pm.monthly_revenue) OVER (PARTITION BY pm.product_id),
    1) AS pct_of_avg
FROM product_monthly AS pm
INNER JOIN top5 AS t ON pm.product_id = t.product_id
ORDER BY pm.product_name, pm.year_month;

WITH method_monthly AS (
    SELECT
        SUBSTR(paid_at, 1, 7) AS year_month,
        method,
        ROUND(SUM(amount), 0) AS method_amount
    FROM payments
    WHERE status = 'completed'
      AND paid_at LIKE '2024%'
    GROUP BY SUBSTR(paid_at, 1, 7), method
),
with_share AS (
    SELECT
        year_month,
        method,
        method_amount,
        ROUND(100.0 * method_amount
            / SUM(method_amount) OVER (PARTITION BY year_month),
        1) AS share_pct
    FROM method_monthly
)
SELECT
    year_month,
    method,
    method_amount,
    share_pct,
    LAG(share_pct, 1) OVER (
        PARTITION BY method
        ORDER BY year_month
    ) AS prev_share_pct,
    ROUND(share_pct - LAG(share_pct, 1) OVER (
        PARTITION BY method
        ORDER BY year_month
    ), 1) AS share_change_pp
FROM with_share
ORDER BY year_month, share_pct DESC;

WITH ordered AS (
    SELECT
        o.customer_id,
        c.name,
        o.order_number,
        o.total_amount,
        LAG(o.total_amount, 1) OVER (
            PARTITION BY o.customer_id
            ORDER BY o.total_amount
        ) AS prev_amount
    FROM orders AS o
    INNER JOIN customers AS c ON o.customer_id = c.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
)
SELECT
    customer_id,
    name,
    order_number,
    prev_amount,
    total_amount,
    total_amount - prev_amount AS gap
FROM ordered
WHERE prev_amount IS NOT NULL
  AND (total_amount - prev_amount) >= 500000
ORDER BY gap DESC
LIMIT 20;

WITH quarterly AS (
    SELECT
        CAST(SUBSTR(ordered_at, 1, 4) AS INTEGER) AS year,
        (CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) + 2) / 3 AS quarter,
        ROUND(SUM(total_amount), 0) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
      AND ordered_at >= '2020-01-01'
    GROUP BY
        CAST(SUBSTR(ordered_at, 1, 4) AS INTEGER),
        (CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) + 2) / 3
)
SELECT
    year,
    quarter,
    revenue,
    order_count,
    LAG(revenue, 4) OVER (ORDER BY year, quarter) AS prev_year_revenue,
    CASE
        WHEN LAG(revenue, 4) OVER (ORDER BY year, quarter) IS NOT NULL
        THEN ROUND(100.0 * (revenue - LAG(revenue, 4) OVER (ORDER BY year, quarter))
            / LAG(revenue, 4) OVER (ORDER BY year, quarter), 1)
    END AS yoy_growth_pct
FROM quarterly
ORDER BY year, quarter;