首页 > 试题广场 >

SaaS产品高价值用户活跃度分析

[编程题]SaaS产品高价值用户活跃度分析
  • 热度指数：256 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

背景

假设你是一家提供在线项目管理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。
算法知识视频讲解
添加回答
牛客733972631号头像 牛客733972631号 
-- user_profile  == 拼接 user_id和user_name   》》 Alice(101)
-- total_activity_score用户的总活跃积分 == 目标用户 在用户事件表 (user_events)中的总分，没有时间条件
-- avg_monthly_events Q1月均事件数  == 目标用户在用户事件表 (user_events) 中的事件，时间条件Q1

-- 目标用户
with target_user as (
    select distinct
        u.user_id
    from 
        users as u
        inner join user_events as ue on u.user_id =ue.user_id
    where
        u.registration_date >= '2025-01-01' and u.registration_date < '2025-07-01'
        and u.plan_type = 'Pro'
        and ue.event_timestamp >= '2025-01-01' and ue.event_timestamp < '2025-04-01'
        and ue.event_type = 'login'   -- 存在 = 至少有一次'login'行为
),

-- 【总】活跃积分
activity_score_tb as (
    select
        user_id
        ,sum(score_level) as total_activity_score
    from 
        (select
            user_id
            ,case 
                when event_type = 'create_task'   then 5
                when event_type = 'export_report' then 10
                when event_type = 'invite_member' then 8
                else 1 
            end as score_level 
        from
            user_events 
        where 
        user_id in (select user_id from target_user)
    ) as score_level_tb 
    group by 
        user_id 
), 

-- Q1月均事件数
events_Q1 as (
select
    user_id
    ,round(count(event_type)/3 ,2) as avg_monthly_events
from 
    user_events
where 
    user_id in (select user_id from target_user)
    and event_timestamp >= '2025-01-01' and event_timestamp < '2025-04-01'
group by 
    user_id 
)


-- 输出 
select
    concat(u.user_name, '(', u.user_id, ')' ) as user_profile
    ,a.total_activity_score
    ,e.avg_monthly_events
from 
    users as u
    inner join activity_score_tb as a on u.user_id = a.user_id
    inner join events_Q1 as e on u.user_id = e.user_id

order by 
    total_activity_score desc, avg_monthly_events desc , u.user_id asc ;

发表于 2026-02-03 22:56:39 回复(1)
Lovnly头像 Lovnly 
with people as (
    select user_id,user_name
    from
        (select u.user_id,u.user_name,sum(case when event_type='login' then 1 else 0 end) as s_log
        from users u
        inner join user_events ue on u.user_id = ue.user_id
        where (u.registration_date between '2025-01-01' and '2025-06-30')
          and u.plan_type='Pro'
          and (date(event_timestamp) between '2025-01-01' and '2025-03-31')
        group by u.user_id,u.user_name) n
    where s_log>=1
)

select concat(user_name,'(',user_id,')') as user_profile,
       total_activity_score,
       round(event_cnt/3,2) as avg_monthly_events
from
    (select u2.user_id,
           u2.user_name,
           sum(case
               when event_type='create_task' then 5
               when event_type='export_report' then 10
               when event_type= 'invite_member' then 8
               else 1 end) as total_activity_score,
           count(event_type) as event_cnt
    from users u2
    inner join user_events e on u2.user_id = e.user_id
    where e.user_id in (select user_id from people)
      and (date(event_timestamp) between '2025-01-01' and '2025-03-31')
    group by u2.user_id,u2.user_name) end
order by total_activity_score desc ,avg_monthly_events desc ,user_id asc

发表于 2026-02-03 18:33:00 回复(0)
牛客194200828号头像 牛客194200828号
筛选时间的时候记得精确到秒。。。 
with temp1 as (
    select user_id,sum(if(event_type='login',1,0)) cnt
    from user_events
    join users using(user_id)
    where 
        registration_date between '2025-01-01 00:00:00' and '2025-06-30 23:59:59'
        and event_timestamp between '2025-01-01 00:00:00' and '2025-03-31 23:59:59'
        and plan_type = 'Pro'
    group by user_id
)

select 
    concat(user_name,'(',user_id,')') user_profile,
    sum(case when event_type = 'create_task' then 5 when event_type = 'export_report' then 10  when event_type = 'invite_member' then 8 else 1 end) total_activity_score,
    round((sum(case when event_timestamp between '2025-01-01 00:00:00' and '2025-03-31 23:59:59' then 1 end)/3),2) avg_monthly_events
from user_events
join users using(user_id)
where user_id in (select user_id from temp1 where cnt>0)
group by user_name,user_id
order by total_activity_score desc,avg_monthly_events desc,user_id


发表于 2026-02-01 18:53:18 回复(0)
提交观点

问题信息

难度：
3条回答 24浏览

热门推荐

相关试题

SaaS产品高价值用户活跃度分析

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号