WITH user_visits AS (
  SELECT user_id, COUNT(DISTINCT visit_date) AS num_visits
  FROM my_table
  GROUP BY user_id
)
SELECT COUNT(*) AS num_returning_users
FROM user_visits
WHERE num_visits > 1;

SELECT 
  user_id, 
  event_name, 
  timestamp, 
  TIMESTAMPDIFF(SECOND, LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp), timestamp) AS time_diff
FROM my_table;

WITH sessions AS (
  SELECT user_id, event_name, timestamp,
    CASE
      -- 第一個事件總是開始一個新會話
      WHEN LAG(timestamp, 1) OVER (PARTITION BY user_id ORDER BY timestamp) IS NULL THEN 1
      -- 檢查事件之間是否超過30分鐘
      WHEN timestamp - LAG(timestamp, 1) OVER (PARTITION BY user_id ORDER BY timestamp) >= INTERVAL '30 minutes' THEN 1
      -- 否則，繼續當前會話
      ELSE 0
    END AS new_session
  FROM my_table
), session_ids AS (
  SELECT user_id, event_name, timestamp, SUM(new_session) OVER (PARTITION BY user_id ORDER BY timestamp) AS user_session_id
  FROM sessions
)
SELECT user_id, event_name, timestamp, new_session, session_id
FROM session_ids
ORDER BY user_id, timestamp;

SELECT
    DATE(timestamp) AS date,
    -- 將user_id和user_session_id連接起來，創建一個全局會話id 
    COUNT(DISTINCT CONCAT(user_id, '-', user_session_id)) AS unique_sessions
  FROM
    my_table
  GROUP BY
    DATE(timestamp)

WITH user_actions AS (
  SELECT
    CONCAT(user_id, '-', user_session_id) AS global_session_id,
    DATE(timestamp) AS date,
    -- 計算每個會話和日期的唯一事件數
    COUNT(DISTINCT timestamp) AS session_event_count
  FROM
    my_table
  GROUP BY
    CONCAT(user_id, '-', user_session_id), DATE(timestamp)
)
SELECT
  date,
  -- 通過計算不同的global_session_id的數量來計算總會話數
  COUNT(DISTINCT global_session_id) AS sessions,
  -- 求和所有會話中的事件數
  SUM(session_event_count) AS total_events,
  -- 將總事件數除以總會話數
  SUM(session_event_count) / COUNT(DISTINCT global_session_id) AS avg_events_per_session
FROM
  user_actions
GROUP BY
  date;

WITH all_users AS (
-- 計算每日所有用戶數
SELECT 
COUNT(DISTINCT users_id) AS total_users, 
DATE(timestamp) AS date
FROM 
my_table
GROUP BY 
date),

returning_users AS (
-- 計算每日回頭用戶數
SELECT 
COUNT(DISTINCT users_id) AS returning_users, 
DATE(timestamp) AS date
FROM 
my_table
WHERE user_session_id > 1
GROUP BY 
date)

SELECT 
-- 連接CTE并將回頭用戶除以總用戶數
all_users.date,
total_users, 
returning_users,
ROUND((returning_users / all_users), 2) AS returning_ratio
FROM all_users
LEFT JOIN returning_users ON returning_users.date = all_users.date

WITH weekly_new_users AS (
-- 計算每周的新用戶數
  SELECT
    DATE_TRUNC('week', timestamp) AS week_start,
    COUNT(DISTINCT user_id) AS weekly_new_users
  FROM 
    my_table
  WHERE user_session_id = 1
  GROUP BY
    DATE_TRUNC('week', timestamp),

weekly_cumulative AS (
-- 對每周新用戶數進行累加
  SELECT
    week_start,
    sum(weekly_new_users) OVER (ORDER BY week_start RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_users
  FROM
    weekly_new_users
)

-- 使用累積用戶計算每周增長率
SELECT
  DATE(week_start) AS week_start,
  cum_users,
  ROUND(((cum_users - LAG(cum_users) OVER (ORDER BY week_start))/LAG(cum_users) OVER (ORDER BY week_start)) * 100, 2) AS weekly_growth_rate,
FROM
  weekly_cumulative