題目背景：健身房會員運動表現分析

你正在管理一家健身房的資料庫。經理想要找出「高強度運動專案」中的「核心學員」，並觀察他們在該專案中的消耗熱量排名。

1. 原始資料準備 (Schema & Data)

請先在你的 PostgreSQL 中執行以下指令：

-- 只是題目不正規化
CREATE TABLE gym_logs (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_name TEXT,       -- 會員名稱
    workout_type TEXT,      -- 運動項目
    calories_burned INT,    -- 消耗熱量
    duration_minutes INT,   -- 運動時長
    log_date DATE
);

INSERT INTO gym_logs (member_name, workout_type, calories_burned, duration_minutes, log_date) VALUES
('阿強', '重訓', 450, 60, '2023-11-01'),
('阿強', '跑步', 600, 45, '2023-11-02'),
('小明', '重訓', 300, 40, '2023-11-01'),
('小明', '瑜珈', 200, 60, '2023-11-02'),
('大華', '重訓', 700, 90, '2023-11-01'),
('大華', '跑步', 800, 60, '2023-11-03'),
('玲玲', '瑜珈', 250, 60, '2023-11-01'),
('玲玲', '重訓', 150, 20, '2023-11-04'),
('阿強', '重訓', 500, 55, '2023-11-05'),
('大華', '重訓', 650, 80, '2023-11-05'),
('小明', '重訓', 400, 50, '2023-11-06');

2. 題目要求

請撰寫一個 SQL 查詢，找出符合以下條件的資料：

• 過濾原始資料 (WHERE)：
  • 只納入「單次運動時間 超過 30 分鐘」的紀錄。
• 過濾群組結果 (HAVING)：
  • 針對「運動項目 (workout_type)」進行分組，且該項目的「平均消耗熱量」必須 大於 400 卡路里 才會出現在最終報表中。
• 統計與排名 (Window Function)：
  • 在符合上述條件的運動項目中，計算每位學員在該項目下的 總消耗熱量，並根據該總熱量在 同一個運動項目 內進行排名（熱量最高的為第 1 名）。
• 預期輸出欄位：

workout_type (運動項目), member_name (學員姓名), total_calories (該項目總消耗熱量), rank_in_type (項目內排名)

這是我的作法

-- 先看一下table內容
select *
 from gym_logs;
 
-- 過濾有超過30分鐘的資料
with t1 as (
 select *
   from gym_logs
  where duration_minutes > 30
), 
-- 平均消耗熱量 大於400
t2 as (
 select workout_type
   from t1
  group by workout_type
 having AVG(calories_burned) > 400
),
-- 根據過濾結果產生每個項目會員總消耗的熱量
t3 as (
select t1.workout_type
    , t1.member_name
    , SUM(t1.calories_burned) as total_calories
 from t1
 join t2
   on t1.workout_type = t2.workout_type
group by t1.workout_type
    , t1.member_name)
select workout_type
    , member_name
    , total_calories
    , RANK() OVER (PARTITION BY t3.workout_type ORDER BY t3.total_calories DESC) AS rank_in_type
 from t3;

邀請下個出題者

大家來玩玩SQL 腦力激盪一下吧

一級屠豬士
麻煩!小雨大出下一題,可以根據這題出變形題或者額外再出一題,出完題目可以邀請下個人出題XD