19강: CTE와 재귀 CTE

WITH RECURSIVE months AS (
SELECT 1 AS month_num
UNION ALL
SELECT month_num + 1
FROM months
WHERE month_num < 12
)
SELECT
m.month_num,
'2024-' || SUBSTR('0' || m.month_num, -2) AS year_month,
COUNT(o.id) AS order_count
FROM months AS m
LEFT JOIN orders AS o
ON SUBSTR(o.ordered_at, 1, 7) = '2024-' || SUBSTR('0' || m.month_num, -2)
AND o.status NOT IN ('cancelled', 'returned')
GROUP BY m.month_num
ORDER BY m.month_num;

WITH customer_recency AS (
SELECT
customer_id,
COUNT(*)        AS order_count,
MAX(ordered_at) AS last_order_date
FROM orders
WHERE status NOT IN ('cancelled', 'returned')
GROUP BY customer_id
)
SELECT
c.id    AS customer_id,
c.name,
c.grade,
cr.order_count,
cr.last_order_date
FROM customer_recency AS cr
INNER JOIN customers AS c ON cr.customer_id = c.id
WHERE cr.order_count >= 3
AND julianday('now') - julianday(cr.last_order_date) > 180
ORDER BY cr.last_order_date ASC;

WITH monthly AS (
SELECT
SUBSTR(ordered_at, 1, 7) AS year_month,
SUM(total_amount)        AS revenue
FROM orders
WHERE ordered_at LIKE '2024%'
AND status NOT IN ('cancelled', 'returned')
GROUP BY SUBSTR(ordered_at, 1, 7)
),
with_lag AS (
SELECT
year_month,
revenue,
LAG(revenue) OVER (ORDER BY year_month) AS prev_revenue
FROM monthly
)
SELECT
year_month,
revenue,
prev_revenue,
ROUND(revenue - prev_revenue, 2) AS mom_change,
ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS mom_pct
FROM with_lag
ORDER BY year_month;

WITH overall AS (
SELECT ROUND(AVG(price), 2) AS overall_avg
FROM products
WHERE is_active = 1
)
SELECT
cat.name AS category_name,
ROUND(AVG(p.price), 2) AS avg_price,
o.overall_avg,
ROUND(AVG(p.price) - o.overall_avg, 2) AS diff_from_overall
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
CROSS JOIN overall AS o
WHERE p.is_active = 1
GROUP BY cat.id, cat.name, o.overall_avg
ORDER BY avg_price DESC;

WITH RECURSIVE category_tree AS (
SELECT
id,
name,
parent_id,
name AS full_path
FROM categories
WHERE parent_id IS NULL

UNION ALL

SELECT
c.id,
c.name,
c.parent_id,
ct.full_path || ' > ' || c.name
FROM categories AS c
INNER JOIN category_tree AS ct ON c.parent_id = ct.id
)
SELECT
ct.id   AS category_id,
ct.name AS category_name,
ct.full_path
FROM category_tree AS ct
WHERE ct.id NOT IN (SELECT parent_id FROM categories WHERE parent_id IS NOT NULL)
ORDER BY ct.full_path;

WITH RECURSIVE tree AS (
SELECT id, name AS root_name, id AS root_id
FROM categories
WHERE parent_id IS NULL

UNION ALL

SELECT c.id, t.root_name, t.root_id
FROM categories AS c
INNER JOIN tree AS t ON c.parent_id = t.id
)
SELECT
t.root_name AS root_category,
COUNT(DISTINCT t.id) - 1 AS subcategory_count,
COUNT(DISTINCT p.id)     AS product_count
FROM tree AS t
LEFT JOIN products AS p ON p.category_id = t.id
GROUP BY t.root_id, t.root_name
ORDER BY product_count DESC;

WITH RECURSIVE org AS (
SELECT id, name, manager_id, 0 AS level
FROM staff
WHERE manager_id IS NULL

UNION ALL

SELECT s.id, s.name, s.manager_id, o.level + 1
FROM staff AS s
INNER JOIN org AS o ON s.manager_id = o.id
)
SELECT
m.name AS manager_name,
m.level,
COUNT(s.id) AS direct_reports
FROM org AS m
LEFT JOIN staff AS s ON s.manager_id = m.id
GROUP BY m.id, m.name, m.level
HAVING COUNT(s.id) > 0
ORDER BY m.level, direct_reports DESC;

WITH method_monthly AS (
SELECT
SUBSTR(p.paid_at, 1, 7) AS year_month,
p.method,
SUM(p.amount) AS amount
FROM payments AS p
INNER JOIN orders AS o ON p.order_id = o.id
WHERE p.paid_at LIKE '2024%'
AND p.status = 'completed'
GROUP BY SUBSTR(p.paid_at, 1, 7), p.method
),
monthly_total AS (
SELECT
year_month,
SUM(amount) AS total
FROM method_monthly
GROUP BY year_month
)
SELECT
mm.year_month,
mm.method,
mm.amount,
mt.total AS monthly_total,
ROUND(100.0 * mm.amount / mt.total, 1) AS pct
FROM method_monthly AS mm
INNER JOIN monthly_total AS mt ON mm.year_month = mt.year_month
ORDER BY mm.year_month, mm.amount DESC;

WITH product_ratings AS (
SELECT
p.id AS product_id,
p.name AS product_name,
cat.name AS category_name,
p.category_id,
ROUND(AVG(r.rating), 2) AS avg_rating,
COUNT(r.id) AS review_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
INNER JOIN reviews AS r ON r.product_id = p.id
GROUP BY p.id, p.name, cat.name, p.category_id
HAVING COUNT(r.id) >= 3
),
ranked AS (
SELECT
category_name,
product_name,
avg_rating,
review_count,
RANK() OVER (
PARTITION BY category_id
ORDER BY avg_rating DESC
) AS rnk
FROM product_ratings
)
SELECT category_name, product_name, avg_rating, review_count, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY category_name, rnk;

WITH sales AS (
SELECT
oi.product_id,
SUM(oi.quantity)              AS units_sold,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi
INNER JOIN orders AS o ON oi.order_id = o.id
WHERE o.status IN ('delivered', 'confirmed')
GROUP BY oi.product_id
),
ratings AS (
SELECT
product_id,
ROUND(AVG(rating), 2) AS avg_rating
FROM reviews
GROUP BY product_id
),
combined AS (
SELECT
p.name AS product_name,
COALESCE(s.units_sold, 0) AS units_sold,
COALESCE(s.revenue, 0)    AS revenue,
r.avg_rating
FROM products AS p
LEFT JOIN sales   AS s ON p.id = s.product_id
LEFT JOIN ratings AS r ON p.id = r.product_id
WHERE p.is_active = 1
)
SELECT product_name, units_sold, revenue, avg_rating
FROM combined
ORDER BY revenue DESC
LIMIT 10;