使用SQL分析，挖掘产品市场数据库的价值

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