SELECT
    e.name,
    e.department,
    e.salary,
    (SELECT AVG(salary) FROM employees e2
     WHERE e2.department = e.department) as dept_avg
FROM employees e;

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

SUM(revenue) OVER (
    PARTITION BY category    -- 어떤 그룹 내에서?
    ORDER BY date            -- 어떤 순서로?
    ROWS BETWEEN             -- 어느 범위까지?
        UNBOUNDED PRECEDING
        AND CURRENT ROW
)

-- 전체 매출 대비 각 제품의 비중
SELECT
    product_name,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER () as total_share,           -- 전체 대비
    revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category) as category_share  -- 카테고리 대비
FROM products;

-- 일별 누적 매출
SELECT
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY date) as cumulative_revenue
FROM daily_sales;

LAG(컬럼, N, 기본값)  -- N행 이전 값 (기본 1)
LEAD(컬럼, N, 기본값) -- N행 이후 값 (기본 1)

WITH monthly_revenue AS (
    SELECT
        strftime('%Y-%m', order_date) as month,
        SUM(total_amount) as revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) as diff,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 /
          LAG(revenue) OVER (ORDER BY month), 1) as growth_pct
FROM monthly_revenue
ORDER BY month;

SELECT
    date,
    revenue,
    LAG(revenue, 7) OVER (ORDER BY date) as same_day_last_week,
    ROUND((revenue - LAG(revenue, 7) OVER (ORDER BY date)) * 100.0 /
          LAG(revenue, 7) OVER (ORDER BY date), 1) as wow_growth
FROM daily_sales
WHERE date >= DATE('now', '-14 days');

SELECT
    product_name,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as row_num,
    RANK() OVER (ORDER BY revenue DESC) as rank,
    DENSE_RANK() OVER (ORDER BY revenue DESC) as dense_rank
FROM products;

-- 카테고리별 TOP 3 제품 (정확히 3개만)
WITH ranked AS (
    SELECT
        category,
        product_name,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

-- TOP 10인데 10위가 동점이면 다 포함
WITH ranked AS (
    SELECT product_name, revenue,
           RANK() OVER (ORDER BY revenue DESC) as rank
    FROM products
)
SELECT * FROM ranked WHERE rank <= 10;
-- 동점 10위가 3명이면 12행 반환

-- 1등, 2등, 3등... 건너뛰기 없이
SELECT product_name, revenue,
       DENSE_RANK() OVER (ORDER BY revenue DESC) as rank
FROM products;

SELECT
    date,
    daily_revenue,
    ROUND(AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) as ma7
FROM daily_sales;

SELECT
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY strftime('%Y-%m', date)  -- 월별로 리셋
        ORDER BY date
    ) as mtd_revenue  -- Month-to-Date
FROM daily_sales;

-- 데이터: 같은 날짜에 여러 주문
-- | date       | amount |
-- |------------|--------|
-- | 2024-01-01 | 100    |
-- | 2024-01-01 | 150    |  ← 같은 날짜
-- | 2024-01-02 | 200    |

-- ROWS: 물리적 행 순서대로
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as rows_sum
FROM orders;
-- 결과: 100, 250, 450

-- RANGE: 같은 값끼리 묶어서
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date RANGE UNBOUNDED PRECEDING) as range_sum
FROM orders;
-- 결과: 250, 250, 450  ← 같은 날짜는 합계가 같음!

WITH customer_cohort AS (
    -- 고객별 첫 구매월
    SELECT
        customer_id,
        strftime('%Y-%m', MIN(order_date)) as cohort_month
    FROM orders WHERE status = 'completed'
    GROUP BY customer_id
),
monthly_activity AS (
    -- 고객별 월별 활동 여부
    SELECT DISTINCT
        o.customer_id,
        cc.cohort_month,
        strftime('%Y-%m', o.order_date) as activity_month
    FROM orders o
    JOIN customer_cohort cc ON o.customer_id = cc.customer_id
    WHERE o.status = 'completed'
)
SELECT
    cohort_month,
    COUNT(DISTINCT customer_id) as M0,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+1 month')) THEN customer_id END) as M1,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+2 month')) THEN customer_id END) as M2,
    COUNT(DISTINCT CASE WHEN activity_month =
        strftime('%Y-%m', DATE(cohort_month || '-01', '+3 month')) THEN customer_id END) as M3
FROM monthly_activity
GROUP BY cohort_month
ORDER BY cohort_month;

SELECT * FROM (
    SELECT * FROM (
        SELECT customer_id, SUM(amount) as total
        FROM orders GROUP BY customer_id
    ) WHERE total > 100000
) WHERE customer_id IN (SELECT customer_id FROM vip_list);

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) as total
    FROM orders
    GROUP BY customer_id
),
high_value AS (
    SELECT * FROM customer_totals
    WHERE total > 100000
)
SELECT hv.*
FROM high_value hv
WHERE hv.customer_id IN (SELECT customer_id FROM vip_list);

WITH daily_metrics AS (
    SELECT
        DATE(order_date) as date,
        SUM(total_amount) as revenue,
        COUNT(*) as orders,
        COUNT(DISTINCT customer_id) as customers
    FROM orders
    WHERE status = 'completed'
      AND order_date >= DATE('now', '-30 days')
    GROUP BY DATE(order_date)
)
SELECT
    date,
    revenue,
    orders,
    customers,

    -- 전일 대비
    LAG(revenue) OVER (ORDER BY date) as prev_day_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY date)) * 100.0 /
          LAG(revenue) OVER (ORDER BY date), 1) as dod_growth,

    -- 7일 전 대비 (같은 요일)
    LAG(revenue, 7) OVER (ORDER BY date) as week_ago_revenue,
    ROUND((revenue - LAG(revenue, 7) OVER (ORDER BY date)) * 100.0 /
          LAG(revenue, 7) OVER (ORDER BY date), 1) as wow_growth,

    -- 7일 이동평균
    ROUND(AVG(revenue) OVER (
        ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) as ma7

FROM daily_metrics
ORDER BY date DESC
LIMIT 14;

-- ❌ 매번 다른 정렬 → 느림
SELECT
    SUM(revenue) OVER (ORDER BY date) as sum1,
    AVG(revenue) OVER (ORDER BY category) as avg1,  -- 다른 정렬
    LAG(revenue) OVER (ORDER BY product_id) as lag1 -- 또 다른 정렬
FROM sales;

-- ✅ 같은 정렬을 공유 → 빠름
SELECT
    SUM(revenue) OVER w as running_sum,
    AVG(revenue) OVER w as running_avg,
    LAG(revenue) OVER w as prev_revenue
FROM sales
WINDOW w AS (ORDER BY date);  -- Named Window

-- PARTITION BY + ORDER BY 컬럼에 인덱스
CREATE INDEX idx_sales_cat_date ON sales(category, date);

-- 이제 이 쿼리가 빨라짐
SELECT
    category,
    date,
    SUM(revenue) OVER (PARTITION BY category ORDER BY date) as cumsum
FROM sales;