背景 假设你是一家提供在线项目管理SaaS公司的数据分析师。为了评估用户的产品使用深度和活跃度，公司希望对付费用户进行分析，识别出高价值的活跃用户，以便为他们提供更好的服务或营销策略。 你需要分析两张表：一张是用户信息表，记录了用户的基本信息和订阅计划；另一张是用户事件表，记录了用户在产品内的各种操作行为。 表结构和字段说明 表1：用户信息表 (users) user_id(INT): 用户的唯一标识符，主键。 user_name(VARCHAR(50)): 用户名。 registration_date(DATE): 用户的注册日期。 plan_type(VARCHAR(20)): 用户的订阅计划类型，例如 'Free', 'Pro', 'Enterprise'。 表2：用户事件表 (user_events) event_id(INT): 事件的唯一标识符，主键。 user_id(INT): 执行该事件的用户的ID。 event_type(VARCHAR(50)): 事件的类型，例如 'login', 'create_task', 'export_report', 'invite_member'。 event_timestamp(DATETIME): 事件发生时的精确时间。 问题 请编写一条SQL查询，统计出在2025年上半年（2025-01-01至2025-06-30）注册的'Pro'计划用户中，在2025年第一季度（2025-01-01至2025-03-31）至少有一次'login'行为的用户的活跃度。 查询结果需要包含以下字段： user_profile(VARCHAR): 用户信息，格式为用户名(用户ID)，例如Alice(101)。 total_activity_score(INT): 用户的总活跃积分。积分规则如下： 'create_task'事件计 5 分。 'export_report'事件计 10 分。 'invite_member'事件计 8 分。 其他所有事件（包括'login'）计 1 分。 avg_monthly_events(DECIMAL(10, 2)): 该用户在2025年第一季度的月均事件数。计算方式为：第一季度的总事件数 3，结果四舍五入保留两位小数。 排序规则： 结果首先按照total_activity_score降序排列，如果积分相同，则按照avg_monthly_events降序排列，如果仍然相同，则按照user_id升序排列。 任务要求： 查询2025年上半年注册的、且在Q1至少登录过一次的'Pro'用户，计算他们的活跃积分、Q1月均事件数，并按指定规则排序。
示例1

输入

-- 创建用户表
CREATE TABLE users (
    user_id INT,
    user_name VARCHAR(50),
    registration_date DATE,
    plan_type VARCHAR(20)
);

-- 创建用户事件表
CREATE TABLE user_events (
    event_id INT,
    user_id INT,
    event_type VARCHAR(50),
    event_timestamp DATETIME
);

-- 插入示例数据
INSERT INTO users (user_id, user_name, registration_date, plan_type) VALUES
(101, 'Alice', '2025-02-15', 'Pro'),
(102, 'Bob', '2025-03-20', 'Free'),
(103, 'Charlie', '2025-04-10', 'Pro'),
(104, 'David', '2025-07-05', 'Pro'),
(105, 'Eve', '2025-01-30', 'Pro');

INSERT INTO user_events (event_id, user_id, event_type, event_timestamp) VALUES
(1, 101, 'login', '2025-02-20 10:00:00'),
(2, 101, 'create_task', '2025-02-21 11:00:00'),
(3, 101, 'export_report', '2025-03-15 14:30:00'),
(4, 102, 'login', '2025-03-22 09:00:00'),
(5, 103, 'login', '2025-04-12 08:00:00'), -- Q2登录，不符合Q1登录条件
(6, 105, 'login', '2025-02-01 18:00:00'),
(7, 105, 'invite_member', '2025-02-05 19:00:00'),
(8, 101, 'invite_member', '2025-04-01 10:00:00'); -- Q2事件，不计入Q1月均

输出

user_profile|total_activity_score|avg_monthly_events
Alice(101)|24|1.00
Eve(105)|9|0.67

说明

  • Alice (101): H1注册Pro，Q1有登录。
    • 总积分 = login(1) + create_task(5) + export_report(10) + invite_member(8) = 24分。(Q2的invite_member也算总分)
    • Q1事件数 = 3。
    • Q1月均 = 3 / 3 = 1.00。
  • Bob (102): Free用户，不符合。
  • Charlie (103): Q1无登录行为，不符合。
  • David (104): H2注册，不符合。
  • Eve (105): H1注册Pro，Q1有登录。
    • 总积分 = login(1) + invite_member(8) = 9分。
    • Q1事件数 = 2。
    • Q1月均 = 2 / 3 = 0.67。
加载中...