sql-客服绩效考核问题

sql-客服绩效考核问题

题目描述

tb1表中存储客服和用户的对话，字段分别表示，发送者id，接受者id，时间戳，消息内容。
dim_server 存储客服id 和名称。
求 客服名字 客服完成用户数 未完成会话 平均响应时长

tb_msg

from_id to_id msg_time msg_content
user1   user2 t1        aa
user2   user1 t2        aa

dim_server
server_id server_name

题目分析

该题多次出现在面试中，算是一道hard难度的sql题目了。这类sql题目主要是将所描述内容用数学方式抽象出来。本题可以细分为以下三个步骤去解答。

1. 将 from_id 和to_id做归一化处理。
2. 对发送的消息进行打标，分清本条消息来自于哪一方。
3. 分别计算三个指标后，用server_name做关联获取最终结果。

代码实现

-- 2023-02-02
with temp1 as (
select
    from_id
    ,to_id
    ,t2.server_name 
    ,msg_time
    ,msg_content
    ,case when t3.server_id is not null then '1' else '2' end flag  -- 1 表示客服发的 2 表示用户发的
    ,if(from_id>to_id,concat(from_id,'-',to_id),concat(to_id,'-',from_id)) relation1
from 
    tb_msg t1
left join
    dim_server t2
on
    t1.to_id = t2.server_id    
left join
    dim_server t3 
on 
    t1.from_id = t3.server_id
)

-- 客服完成用户数
select
    server_name
    ,sum(case when flag = 1 then 1 else 0 end) user_cnt -- 客服完成的用户数
from
    (
    select
        server_name
        ,flag
        ,relation1
        ,lead(from_id,1,'-999') over(partition by relation1 order by msg_time) next_user
    from
        temp1 t1
    )aa
where
    next_user = '-999'
group by 
    server_name

从一名不羁的码农开始，谈风月之余谈技术