-- 基本集計
SELECT
    COUNT(*)           AS total_users,    -- NULL含む全行数
    COUNT(email)       AS users_with_email,-- NULLを除いた行数
    COUNT(DISTINCT country) AS countries, -- ユニーク数
    SUM(total)         AS revenue,
    AVG(age)           AS avg_age,
    MAX(created_at)    AS latest,
    MIN(created_at)    AS earliest
FROM users;

-- GROUP BY: グループ別集計
SELECT
    country,
    COUNT(*)   AS user_count,
    AVG(age)   AS avg_age,
    SUM(total) AS total_revenue
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY country;

-- HAVING: グループに対する条件（WHERE は使えない）
SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) >= 100  -- 100人以上の国だけ
ORDER BY cnt DESC;

-- ROW_NUMBER: 連番
SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;

-- PARTITION BY: グループ内でのランキング
SELECT
    department,
    name,
    salary,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
    -- RANK: 同順位の次は飛ぶ (1,2,2,4)
    -- DENSE_RANK: 同順位の次は飛ばない (1,2,2,3)
FROM employees;

-- 集計ウィンドウ関数
SELECT
    order_date,
    total,
    SUM(total)  OVER (ORDER BY order_date) AS running_total,
    AVG(total)  OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d,
    LAG(total,  1, 0) OVER (ORDER BY order_date) AS prev_day,
    LEAD(total, 1, 0) OVER (ORDER BY order_date) AS next_day
FROM daily_sales;