文章旨在幫助大家抱團取暖，希望對小夥伴筆試會有所幫助。

1 app連續三天登入

題目介紹

使用者登入行為日志中記錄使用者id、登入時間字段，統計日志中連續三天登入的使用者數，資料示意如下:

uidlogin_time0012022-11-010012022-11-030022022-11-010022022-11-020022022-11-03

題目分析

這是個很經典的SQL筆試題，也是出鏡率最高的類型。連續登入的含義如上圖（使用者002在2022-11-01~2022-11-03均有記錄)，指的是使用者在某一時間段内每天均有登入記錄。

通過分析可知連續登入時使用者登入時間和其對應排名內插補點相等，這裡使用uid開窗排序 + date_sub(dt,rank)來計算。

解題方法

1) 實作思路

将使用者分組并按照時間排序，并記錄rank排名
計算dt-rank的內插補點，內插補點與使用者共同分組
統計count并找出 count > 3的使用者

2）SQL代碼

select
  userid
  ,min(dt) as start_date
  ,max(dt) as end_date
  ,count(1) as times
from
(
  select 
    userid
    ,dt
    ,date_sub(dt, rn) as date_diff
    from
    (
      select 
        userid
        ,dt
        ,row_number() over(partition by
          userid order by dt) as rn
      from 
        user_tables
    )
)
group by 
  userid, date_diff
having times >= 3

2 日/周/月留存

題目介紹

使用者登入行為日志中記錄使用者id、登陸時間、登入app字段，計算app某天的3日/7日/14日/30日使用者留存。資料示意如下:

uiddtappid0012022-11-01app010022022-11-03app010012022-11-03app020022022-11-02app010012022-11-05app01

題目分析

在網際網路場景中使用者留存是衡量APP使用者活躍的重要名額之一，留存越高，則代表使用者粘性越好。留存的含義為使用者在時間t1活躍同時在時間t2再次活躍。

計算留存需要找到兩個時間點，一個是統計日期t1（如2022-11-01），另一個點為留存時間t2(2022-11-01後的7日/14日/30日區間)，兩段資料進行關聯且均有記錄，最後取日期差即可得到留存。

解題方法

1）實作思路

擷取統計日期時間段資料并根據dt,appid,uid分組groupby
擷取留存時間段資料并根據dt,appid,uid分組groupby
join關聯擷取時間差，分别統計3日/14日/21日/30日留存

2）SQL代碼

select 
  t1.appid,
  count(t1.uid) as active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=1 then t2.uid end) as day2_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=2 then t2.uid end) as day3_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=6 then t2.uid end) as day7_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=13 then t2.uid end) as day14_active_users,
  count(case when datediff(t2.cu_dt,t1.cu_dt)=29 then t2.uid end) as day30_active_users,
  t1.dt
from 
(select
  apptypeid,
  uid,
  dt,
  from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt
from user_tables
where dt='${startDate}'
group by appid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd')
)t1
left join
(select
  apptypeid,
  uid,
  dt,
  from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt
from user_tables
where dt>'${startDate}' and dt<='${startDate+29d}'
group by appid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd')
) t2
on t1.appid=t2.appid and t1.uid=t2.uid
group by t1.appid,t1.dt

3 某時刻app最多線上人數

題目介紹

使用者行為日志中記錄使用者id、appid、進入時間、退出時間字段，計算同一時刻的app的最大人數，資料示意如下:

uidapp_idin_timeout_time001app012022-11-01 11:00:012022-11-01 11:01:01002app022022-11-01 11:03:022022-11-01 11:05:02003app012022-11-01 11:03:092022-11-01 11:05:10004app022022-11-01 11:07:102022-11-01 11:11:10004app012022-11-01 11:09:102022-11-01 11:08:10

題目分析

此為經典狀态計算SQL，常用于統計某時刻某狀态的最大線上人數（直播、商場、車進出流等）。

此類問題最重要的是計算瞬時的最大計數，采用編碼+合并的方法，将進标志為+1，出标志為-1，最後union結果并根據appid進行sum（）開窗并排序。

解題方法

1）實作思路

計算瞬時計數。查詢進出記錄同時設定标志位，結果union合并
根據appid開窗sum，組内根據dt升序和标志位降序

2）SQL代碼

SELECT
  app_id,
  MAX(cnt) max_uv
FROM (
  SELECT
    appid,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) cnt
  FROM (
    SELECT 
      app_id, in_time dt, 1 diff
    FROM user_tables
    WHERE appid != 0
    UNION ALL
    SELECT 
      app_id, out_time dt, -1 diff
    FROM user_tables
    WHERE appid != 0) t1 
) t2
GROUP BY 1
ORDER BY 2 DESC

4 計算部門除去最高和最低的平均收入

題目介紹

部門員工基本資訊表中記錄部門id、員工id、收入字段，計算部門員工的平均收入，資料示意如下:

dep_nouidsalaryd_00100118700d_00100123000d_00100215000d_00100211000d_00100230000

題目分析

使用視窗函數降序和升序分别排序取出最高和最低收入，計算平均收入。

解題方法

1）實作思路

使用開窗函數，分别根據收入組内正序倒序排序
計算平均收入

2）SQL代碼

select a.dep_no，avg(a.salary)
from  
 (
 select *, rank() over( partition by dep_no order by salary ) as rk_1
 , rank() over( partition by dep_no order by salary desc) as rk_2 
 from user_tables
 )  a 
group by a.dep_no
where a.rk_1 >1 and a.rk_2 >1

5 計算分類top3銷售商品

題目介紹

商品浏覽表記錄：商品id、使用者id字段，計算每個商品類别下最受歡迎的Top3産品，資料示意如下:

p_iduidp_001001p_002002P_001003p_002004P_001005

題目分析

這是一個非常常見計算前幾名TopN的場景，一般先計算每個商品被每個使用者浏覽的次數，最後開窗計算商品浏覽次數排序。

解題方法

1）實作思路

計算每個商品下每個使用者的浏覽次數，放在臨時表
開窗函數計算每個商品被浏覽次數排名
計算每個商品浏覽前3的使用者

2）SQL代碼

select
p_id,
uid,
cnt
from
(select
    p_id,
    uid,
    cnt,
    rank() over(partition by p_id order by cnt) rn
  from (
     select
       p_id,
       uid,
       count(*) cnt
    from user_tables
    group by
       p_id,
       uid
  )t1
) t2
where rn<=3;

歡迎大家踴躍投稿和建議。

大資料筆試之SQL必看篇

1 app連續三天登入

題目介紹

題目分析

解題方法

2 日/周/月留存

題目介紹

題目分析

解題方法

3 某時刻app最多線上人數

題目介紹

題目分析

解題方法

4 計算部門除去最高和最低的平均收入

題目介紹

題目分析

解題方法

5 計算分類top3銷售商品

題目介紹

題目分析

解題方法