SQL 주요 기능

-- 전통적 SQL: 실행 순서와 작성 순서가 다릅니다
SELECT city, COUNT(*) AS cnt
FROM customers
WHERE signup_date >= '2025-01-01'
GROUP BY city
HAVING cnt >= 10
ORDER BY cnt DESC
LIMIT 5;

-- 파이프 구문: 위에서 아래로 자연스럽게 읽힙니다
FROM customers
|> WHERE signup_date >= '2025-01-01'
|> AGGREGATE COUNT(*) AS cnt GROUP BY city
|> WHERE cnt >= 10
|> ORDER BY cnt DESC
|> LIMIT 5;

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('MONTH', order_date) AS month,
        SUM(amount) AS revenue
    FROM silver_orders
    GROUP BY 1
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
            / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS growth_pct
    FROM monthly_sales
)
SELECT * FROM growth ORDER BY month;

-- ❌ 전통적 방식: 서브쿼리 필요
SELECT * FROM (
    SELECT
        customer_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
WHERE rn = 1;

-- ✅ QUALIFY 사용: 서브쿼리 불필요
SELECT
    customer_id,
    order_date,
    amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

-- 배열 데이터를 행으로 펼치기
SELECT
    order_id,
    customer_id,
    EXPLODE(items) AS item
FROM orders;

SELECT
    event_id,
    raw_data:user_id::INT          AS user_id,
    raw_data:event_type::STRING    AS event_type,
    raw_data:metadata.browser::STRING AS browser,
    raw_data:tags[0]::STRING       AS first_tag
FROM raw_events;