18강: 윈도우 함수

SELECT
name    AS product_name,
price,
DENSE_RANK() OVER (ORDER BY price DESC) AS overall_rank
FROM products
WHERE is_active = 1
ORDER BY overall_rank
LIMIT 10;

SELECT
year,
new_signups,
SUM(new_signups) OVER (ORDER BY year) AS cumulative_customers
FROM (
SELECT
SUBSTR(created_at, 1, 4) AS year,
COUNT(*)                 AS new_signups
FROM customers
GROUP BY SUBSTR(created_at, 1, 4)
) AS yearly
ORDER BY year;

SELECT
year_month,
revenue,
LAG(revenue, 12) OVER (ORDER BY year_month) AS same_month_last_year,
ROUND(
100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY year_month))
/ LAG(revenue, 12) OVER (ORDER BY year_month),
1
) AS yoy_growth_pct
FROM (
SELECT
SUBSTR(ordered_at, 1, 7) AS year_month,
SUM(total_amount)        AS revenue
FROM orders
WHERE status NOT IN ('cancelled', 'returned')
AND ordered_at BETWEEN '2022-01-01' AND '2024-12-31 23:59:59'
GROUP BY SUBSTR(ordered_at, 1, 7)
) AS monthly
WHERE year_month >= '2023-01'
ORDER BY year_month;

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

SELECT
cat.name AS category_name,
p.name   AS product_name,
p.price,
RANK()       OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS dense_rank
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
ORDER BY cat.name, rank
LIMIT 15;

SELECT
year_month,
monthly_revenue,
ROUND(
AVG(monthly_revenue) OVER (
ORDER BY year_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2
) AS moving_avg_3m
FROM (
SELECT
SUBSTR(ordered_at, 1, 7) AS year_month,
SUM(total_amount)        AS monthly_revenue
FROM orders
WHERE ordered_at LIKE '2024%'
AND status NOT IN ('cancelled', 'returned')
GROUP BY SUBSTR(ordered_at, 1, 7)
) AS monthly
ORDER BY year_month;

SELECT
name,
grade,
total_spent,
quartile
FROM (
SELECT
c.name,
c.grade,
SUM(o.total_amount) AS total_spent,
NTILE(4) OVER (ORDER BY SUM(o.total_amount) DESC) AS quartile
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status NOT IN ('cancelled', 'returned')
GROUP BY c.id, c.name, c.grade
) AS ranked
ORDER BY quartile, total_spent DESC
LIMIT 20;

SELECT
p.name       AS product_name,
o.ordered_at,
oi.quantity,
SUM(oi.quantity) OVER (
ORDER BY o.ordered_at, o.id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_qty
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 oi.product_id = 1
AND o.status NOT IN ('cancelled', 'returned')
ORDER BY o.ordered_at;

SELECT
department,
name,
role,
hired_at,
COUNT(*) OVER (
PARTITION BY department
ORDER BY hired_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_headcount,
COUNT(*) OVER (
PARTITION BY department
) AS dept_total_headcount
FROM staff
WHERE is_active = 1
ORDER BY department, hired_at;

SELECT
customer_id,
name,
order_count,
ROUND(AVG(days_gap), 1) AS avg_days_between_orders
FROM (
SELECT
c.id   AS customer_id,
c.name,
COUNT(*) OVER (PARTITION BY o.customer_id) AS order_count,
ROUND(
julianday(o.ordered_at)
- julianday(LAG(o.ordered_at) OVER (
PARTITION BY o.customer_id ORDER BY o.ordered_at
)),
0
) AS days_gap
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.status NOT IN ('cancelled', 'returned')
) AS gaps
WHERE days_gap IS NOT NULL
GROUP BY customer_id, name, order_count
HAVING order_count >= 5
ORDER BY avg_days_between_orders
LIMIT 15;

SELECT
cat.name  AS category,
p.name    AS product_name,
p.price,
FIRST_VALUE(p.name) OVER (
PARTITION BY p.category_id
ORDER BY p.price
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS cheapest_in_category,
LAST_VALUE(p.name) OVER (
PARTITION BY p.category_id
ORDER BY p.price
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS priciest_in_category
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
ORDER BY cat.name, p.price
LIMIT 15;