CTE Applications

WITH monthly_revenue AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        ROUND(SUM(total_amount), 0) AS revenue,
        COUNT(*) AS order_count
    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,
    order_count,
    (SELECT ROUND(AVG(revenue), 0) FROM monthly_revenue) AS avg_revenue,
    CASE
        WHEN revenue >= (SELECT AVG(revenue) FROM monthly_revenue) THEN '평균 이상'
        ELSE '평균 미만'
    END AS status
FROM monthly_revenue
WHERE revenue >= (SELECT AVG(revenue) FROM monthly_revenue)
ORDER BY revenue DESC;

WITH customer_totals AS (
    SELECT
        c.id,
        c.name,
        c.grade,
        ROUND(SUM(o.total_amount), 0) AS total_spent,
        COUNT(*) AS order_count
    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,
    order_count,
    total_spent
FROM ranked
WHERE rn <= 5
ORDER BY
    CASE grade
        WHEN 'VIP' THEN 1
        WHEN 'GOLD' THEN 2
        WHEN 'SILVER' THEN 3
        WHEN 'BRONZE' THEN 4
    END,
    rn;

WITH sales AS (
    SELECT
        oi.product_id,
        ROUND(SUM(oi.quantity * oi.unit_price), 0) AS total_revenue,
        SUM(oi.quantity) AS units_sold
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    WHERE o.ordered_at LIKE '2024%'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY oi.product_id
),
review_summary AS (
    SELECT
        product_id,
        COUNT(*) AS review_count,
        ROUND(AVG(rating), 2) AS avg_rating
    FROM reviews
    GROUP BY product_id
)
SELECT
    p.name          AS product_name,
    cat.name        AS category,
    s.units_sold,
    s.total_revenue,
    COALESCE(r.review_count, 0) AS review_count,
    r.avg_rating
FROM sales AS s
INNER JOIN products   AS p   ON s.product_id  = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
LEFT  JOIN review_summary AS r ON s.product_id = r.product_id
ORDER BY s.total_revenue DESC
LIMIT 10;

WITH monthly_signups AS (
    SELECT
        SUBSTR(created_at, 1, 7) AS year_month,
        COUNT(*) AS signup_count
    FROM customers
    WHERE created_at >= '2022-01-01'
    GROUP BY SUBSTR(created_at, 1, 7)
),
with_growth AS (
    SELECT
        year_month,
        signup_count,
        LAG(signup_count, 1) OVER (ORDER BY year_month) AS prev_count,
        ROUND(100.0 * (signup_count - LAG(signup_count, 1) OVER (ORDER BY year_month))
            / LAG(signup_count, 1) OVER (ORDER BY year_month), 1) AS growth_pct
    FROM monthly_signups
)
SELECT
    year_month,
    signup_count,
    prev_count,
    growth_pct
FROM with_growth
WHERE growth_pct >= 20
ORDER BY growth_pct DESC;

WITH category_revenue AS (
    SELECT
        cat.name AS category,
        ROUND(SUM(oi.quantity * oi.unit_price), 0) AS 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
    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 cat.name
),
with_share AS (
    SELECT
        category,
        revenue,
        ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS share_pct
    FROM category_revenue
)
SELECT
    category,
    revenue,
    share_pct,
    ROUND(SUM(share_pct) OVER (
        ORDER BY revenue DESC
        ROWS UNBOUNDED PRECEDING
    ), 1) AS cumulative_pct
FROM with_share
ORDER BY revenue DESC;

WITH RECURSIVE cat_tree AS (
    -- 앵커: 최상위 카테고리
    SELECT
        id,
        parent_id,
        name,
        name AS full_path,
        depth,
        0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 재귀: 자식 카테고리
    SELECT
        c.id,
        c.parent_id,
        c.name,
        ct.full_path || ' > ' || c.name,
        c.depth,
        ct.level + 1
    FROM categories AS c
    INNER JOIN cat_tree AS ct ON c.parent_id = ct.id
)
SELECT
    id,
    full_path,
    level,
    depth
FROM cat_tree
ORDER BY full_path;

WITH RECURSIVE org_tree AS (
    -- 앵커: 최고 관리자
    SELECT
        id,
        name,
        department,
        role,
        manager_id,
        name AS chain,
        0 AS depth
    FROM staff
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀: 부하 직원
    SELECT
        s.id,
        s.name,
        s.department,
        s.role,
        s.manager_id,
        ot.chain || ' > ' || s.name,
        ot.depth + 1
    FROM staff AS s
    INNER JOIN org_tree AS ot ON s.manager_id = ot.id
)
SELECT
    id,
    name,
    department,
    role,
    depth,
    chain
FROM org_tree
ORDER BY chain;

WITH RECURSIVE subordinates AS (
    -- 앵커: 각 직원을 자기 자신의 루트로 시작
    SELECT
        id AS root_id,
        id AS member_id
    FROM staff
    WHERE is_active = 1

    UNION ALL

    -- 재귀: 부하 직원 추가
    SELECT
        sub.root_id,
        s.id
    FROM staff AS s
    INNER JOIN subordinates AS sub ON s.manager_id = sub.member_id
    WHERE s.is_active = 1
)
SELECT
    s.id,
    s.name,
    s.department,
    s.role,
    COUNT(*) - 1 AS total_subordinates
FROM subordinates AS sub
INNER JOIN staff AS s ON sub.root_id = s.id
GROUP BY s.id, s.name, s.department, s.role
HAVING COUNT(*) > 1
ORDER BY total_subordinates DESC;

WITH rfm_raw AS (
    SELECT
        c.id            AS customer_id,
        c.name,
        c.grade,
        MAX(o.ordered_at) AS last_order_date,
        COUNT(*)          AS frequency,
        ROUND(SUM(o.total_amount), 0) AS monetary
    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
),
rfm_scored AS (
    SELECT
        *,
        NTILE(4) OVER (ORDER BY last_order_date ASC) AS r_score,
        NTILE(4) OVER (ORDER BY frequency ASC)       AS f_score,
        NTILE(4) OVER (ORDER BY monetary ASC)         AS m_score
    FROM rfm_raw
),
rfm_segment AS (
    SELECT
        *,
        r_score + f_score + m_score AS total_score,
        CASE
            WHEN r_score + f_score + m_score >= 10 THEN 'Champions'
            WHEN r_score + f_score + m_score >= 8  THEN 'Loyal'
            WHEN r_score + f_score + m_score >= 5  THEN 'Potential'
            ELSE 'At Risk'
        END AS segment
    FROM rfm_scored
)
SELECT
    segment,
    COUNT(*)                     AS customer_count,
    ROUND(AVG(frequency), 1)     AS avg_frequency,
    ROUND(AVG(monetary), 0)      AS avg_monetary,
    ROUND(AVG(r_score + f_score + m_score), 1) AS avg_score
FROM rfm_segment
GROUP BY segment
ORDER BY avg_score DESC;

WITH RECURSIVE cat_root AS (
    -- 앵커: 최상위 카테고리 (자기 자신이 루트)
    SELECT
        id,
        id   AS root_id,
        name AS root_name
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 재귀: 자식 카테고리는 부모의 루트를 상속
    SELECT
        c.id,
        cr.root_id,
        cr.root_name
    FROM categories AS c
    INNER JOIN cat_root AS cr ON c.parent_id = cr.id
)
SELECT
    cr.root_name                  AS top_category,
    COUNT(DISTINCT p.id)          AS product_count,
    ROUND(SUM(oi.quantity * oi.unit_price), 0) AS total_revenue,
    SUM(oi.quantity)              AS units_sold
FROM cat_root AS cr
INNER JOIN products   AS p  ON p.category_id = cr.id
INNER JOIN order_items AS oi ON oi.product_id = p.id
INNER JOIN orders     AS o  ON oi.order_id   = o.id
WHERE o.ordered_at LIKE '2024%'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY cr.root_id, cr.root_name
ORDER BY total_revenue DESC;

WITH RECURSIVE months AS (
    SELECT '2024-01' AS year_month
    UNION ALL
    SELECT SUBSTR(DATE(year_month || '-01', '+1 month'), 1, 7)
    FROM months
    WHERE year_month < '2024-12'
),
actual_revenue AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        COUNT(*)                 AS order_count,
        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
    m.year_month,
    COALESCE(a.order_count, 0) AS order_count,
    COALESCE(a.revenue, 0)     AS revenue
FROM months AS m
LEFT JOIN actual_revenue AS a ON m.year_month = a.year_month
ORDER BY m.year_month;

WITH product_sales AS (
    SELECT
        p.id       AS product_id,
        p.name     AS product_name,
        ROUND(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
        SUM(oi.quantity) AS units_sold
    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
),
tiered AS (
    SELECT
        *,
        NTILE(5) OVER (ORDER BY revenue DESC) AS tier
    FROM product_sales
),
tier_summary AS (
    SELECT DISTINCT
        tier,
        COUNT(*) OVER (PARTITION BY tier) AS product_count,
        MIN(revenue) OVER (PARTITION BY tier) AS min_revenue,
        MAX(revenue) OVER (PARTITION BY tier) AS max_revenue,
        ROUND(AVG(revenue) OVER (PARTITION BY tier), 0) AS avg_revenue,
        FIRST_VALUE(product_name) OVER (
            PARTITION BY tier ORDER BY revenue DESC
        ) AS top_product
    FROM tiered
)
SELECT
    tier,
    CASE tier
        WHEN 1 THEN 'S (최상위)'
        WHEN 2 THEN 'A (우수)'
        WHEN 3 THEN 'B (보통)'
        WHEN 4 THEN 'C (저조)'
        WHEN 5 THEN 'D (부진)'
    END AS tier_label,
    product_count,
    min_revenue,
    max_revenue,
    avg_revenue,
    top_product
FROM tier_summary
ORDER BY tier;

WITH RECURSIVE date_seq AS (
    SELECT '2024-01-01' AS date_val
    UNION ALL
    SELECT DATE(date_val, '+1 day')
    FROM date_seq
    WHERE date_val < '2024-12-31'
),
daily_orders AS (
    SELECT
        DATE(ordered_at) AS order_date,
        COUNT(*)         AS order_count
    FROM orders
    WHERE ordered_at >= '2024-01-01'
      AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY DATE(ordered_at)
)
SELECT
    d.date_val AS gap_date,
    CASE CAST(STRFTIME('%w', d.date_val) AS INTEGER)
        WHEN 0 THEN '일'
        WHEN 1 THEN '월'
        WHEN 2 THEN '화'
        WHEN 3 THEN '수'
        WHEN 4 THEN '목'
        WHEN 5 THEN '금'
        WHEN 6 THEN '토'
    END AS day_name
FROM date_seq AS d
LEFT JOIN daily_orders AS o ON d.date_val = o.order_date
WHERE o.order_count IS NULL
ORDER BY d.date_val;

WITH cohort AS (
    SELECT
        customer_id,
        SUBSTR(MIN(ordered_at), 1, 7) AS cohort_month
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY customer_id
),
order_months AS (
    SELECT DISTINCT
        customer_id,
        SUBSTR(ordered_at, 1, 7) AS order_month
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
),
cohort_activity AS (
    SELECT
        co.cohort_month,
        (CAST(SUBSTR(om.order_month, 1, 4) AS INTEGER) * 12
         + CAST(SUBSTR(om.order_month, 6, 2) AS INTEGER))
      - (CAST(SUBSTR(co.cohort_month, 1, 4) AS INTEGER) * 12
         + CAST(SUBSTR(co.cohort_month, 6, 2) AS INTEGER)) AS months_since,
        COUNT(DISTINCT om.customer_id) AS active_customers
    FROM cohort AS co
    INNER JOIN order_months AS om ON co.customer_id = om.customer_id
    GROUP BY co.cohort_month, months_since
),
cohort_size AS (
    SELECT
        cohort_month,
        COUNT(*) AS total_customers
    FROM cohort
    GROUP BY cohort_month
)
SELECT
    ca.cohort_month,
    cs.total_customers,
    ca.months_since,
    ca.active_customers,
    ROUND(100.0 * ca.active_customers / cs.total_customers, 1) AS retention_pct
FROM cohort_activity AS ca
INNER JOIN cohort_size AS cs ON ca.cohort_month = cs.cohort_month
WHERE ca.cohort_month >= '2024-01'
  AND ca.months_since <= 6
ORDER BY ca.cohort_month, ca.months_since;

WITH RECURSIVE cat_tree AS (
    SELECT
        id,
        parent_id,
        name,
        depth,
        sort_order,
        PRINTF('%03d', sort_order) AS sort_path,
        0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.parent_id,
        c.name,
        c.depth,
        c.sort_order,
        ct.sort_path || '/' || PRINTF('%03d', c.sort_order),
        ct.level + 1
    FROM categories AS c
    INNER JOIN cat_tree AS ct ON c.parent_id = ct.id
),
direct_products AS (
    SELECT category_id, COUNT(*) AS direct_count
    FROM products
    WHERE is_active = 1
    GROUP BY category_id
)
SELECT
    ct.id,
    CASE ct.level
        WHEN 0 THEN ct.name
        WHEN 1 THEN '  ' || ct.name
        WHEN 2 THEN '    ' || ct.name
    END AS tree_display,
    ct.level,
    COALESCE(dp.direct_count, 0) AS direct_products
FROM cat_tree AS ct
LEFT JOIN direct_products AS dp ON ct.id = dp.category_id
ORDER BY ct.sort_path;