SELECT dept, name, salary
FROM (
  SELECT *,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) sub
WHERE rnk <= 3;

SELECT dept, name, salary
FROM employees
QUALIFY RANK() OVER (PARTITION BY dept ORDER BY salary DESC) <= 3;

-- 创建销售数据
CREATE TABLE sales AS
SELECT * FROM (VALUES
  ('华东', '张三', '2026-01', 85000),
  ('华东', '李四', '2026-01', 92000),
  ('华东', '王五', '2026-01', 78000),
  ('华东', '赵六', '2026-01', 105000),
  ('华东', '钱七', '2026-02', 88000),
  ('华南', '孙八', '2026-01', 95000),
  ('华南', '周九', '2026-01', 72000),
  ('华南', '吴十', '2026-01', 110000),
  ('华南', '郑十一', '2026-02', 87000),
  ('华北', '冯十二', '2026-01', 65000),
  ('华北', '陈十三', '2026-01', 89000),
  ('华北', '褚十四', '2026-01', 92000)
) AS t(region, salesperson, month, amount);

-- 取每个区域销售额 Top 2
SELECT region, salesperson, month, amount
FROM sales
QUALIFY RANK() OVER (
  PARTITION BY region 
  ORDER BY amount DESC
) <= 2
ORDER BY region, amount DESC;

-- 用户事件数据
CREATE TABLE user_events AS
SELECT * FROM (VALUES
  ('user_001', 'login', '2026-05-29 10:30:00'),
  ('user_001', 'purchase', '2026-05-29 10:35:00'),
  ('user_001', 'logout', '2026-05-29 11:00:00'),
  ('user_002', 'login', '2026-05-29 09:00:00'),
  ('user_002', 'view_item', '2026-05-29 09:15:00'),
  ('user_002', 'purchase', '2026-05-29 09:20:00'),
  ('user_003', 'login', '2026-05-28 22:00:00')
) AS t(user_id, event, event_time);

-- 取每个用户的最后一条事件
SELECT user_id, event, event_time
FROM user_events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id 
  ORDER BY event_time DESC
) = 1;

-- 每日营收数据
CREATE TABLE daily_revenue AS
SELECT * FROM (VALUES
  ('2026-05-20', 12000),
  ('2026-05-21', 13500),
  ('2026-05-22', 11000),
  ('2026-05-23', 8500),
  ('2026-05-24', 9000),
  ('2026-05-25', 14000),
  ('2026-05-26', 16000),
  ('2026-05-27', 15500),
  ('2026-05-28', 17000),
  ('2026-05-29', 10000)
) AS t(dt, revenue);

-- 发现营收环比下降超过 20% 的日期
SELECT dt, revenue,
  ROUND((revenue - LAG(revenue) OVER (ORDER BY dt)) 
    / NULLIF(LAG(revenue) OVER (ORDER BY dt), 0) * 100, 1) AS pct_change
FROM daily_revenue
QUALIFY (revenue - LAG(revenue) OVER (ORDER BY dt)) 
    / NULLIF(LAG(revenue) OVER (ORDER BY dt), 0) < -0.15;

EXPLAIN
SELECT dept, name, salary
FROM employees
QUALIFY RANK() OVER (PARTITION BY dept ORDER BY salary DESC) <= 3;

-- ❌ 子查询：阅读时需要跟踪两个 SELECT 层级
SELECT dept, name, salary FROM (
  SELECT *, RANK() OVER (...) AS rnk FROM employees
) WHERE rnk <= 3;

-- ✅ QUALIFY：线性阅读，无需跳转
SELECT dept, name, salary FROM employees
QUALIFY RANK() OVER (...) <= 3;

-- ❌ 错误：QUALIFY 中不能引用非窗口函数表达式
SELECT dept, name, salary
FROM employees
QUALIFY salary > 10000 AND RANK() OVER (...) <= 3;

-- ✅ 正确：用 WHERE 过滤列，用 QUALIFY 过滤窗口函数
SELECT dept, name, salary
FROM employees
WHERE salary > 10000
QUALIFY RANK() OVER (...) <= 3;

SELECT ...
FROM ...
WHERE ...              -- 先过滤原始行
GROUP BY ...           -- 再聚合
HAVING ...             -- 再过滤聚合结果
QUALIFY ...            -- 再过滤窗口函数结果
ORDER BY ...           -- 最后排序
LIMIT ...;

-- ❌ 错误：SELECT 别名在 QUALIFY 之后才可用
SELECT 
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank <= 10;

-- ✅ 正确：直接写窗口函数表达式
SELECT 
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY RANK() OVER (ORDER BY salary DESC) <= 10;

-- 1. 先清洗数据
WITH cleaned_events AS (
  SELECT user_id, event, event_time
  FROM raw_events
  WHERE event IS NOT NULL
),
-- 2. 取每个用户的最新事件
latest_events AS (
  SELECT user_id, event, event_time
  FROM cleaned_events
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY user_id 
    ORDER BY event_time DESC
  ) = 1
),
-- 3. 做聚合分析
user_stats AS (
  SELECT 
    DATE_TRUNC('day', event_time) AS active_date,
    COUNT(*) AS active_users
  FROM latest_events
  GROUP BY active_date
)
-- 4. 最终输出
SELECT * FROM user_stats
ORDER BY active_date DESC;