【面試題】 某遊戲數據後台設有「登入日誌」和「登出日誌」兩張表。
「登入日誌」記錄各玩家的登入時間和登入時的角色等級。
「登出日誌」記錄各玩家的登出時間和登出時的角色等級。
其中，「角色id」欄位唯一辨識玩家。
遊戲開服前兩天（2022-08-13至2022-08-14）的角色登入和登出日誌如下
一天中，玩家可以多次登入登出遊戲，請使用SQL分析出以下業務問題：
分析開服首日（2022-08-13），遊戲的DAU（日活躍玩家數）和次日留存率（次日仍登入的活躍玩家數/當日活躍玩家總數）
【解題思路】
問題1： 分析開服首日（2022-08-13），遊戲的DAU（日活躍玩家數）和次日留存率（次日仍登入的活躍玩家數/當日活躍玩家總數）
1.計算開服首日遊戲的DAU（日活躍玩家數）
遊戲的DAU（日活躍玩家數），第一步就是要明確什麽是日活躍玩家數，日活躍玩家數表示當日至少登入過遊戲一次的不重復玩家數。
根據題意可知，當日即為開服首日（2022-08-13），因此，我們要用where子句篩選出日期為「2022-08-13」的數據：
1 where 日期 = '2022-08-13'
至少登入過遊戲一次表示登入過遊戲即可，「登入日誌」記錄玩家的登入資訊，玩家有登入過遊戲就會有記錄。
而在「登出日誌」中玩家登出資訊有可能缺失，因此，為了避免漏算玩家，我們從「登入日誌」取數據進行查詢：
1 from 登入日誌
如何計算玩家數呢？
計算玩家數即為計算玩家數量，而count()函式具有計數功能，因此我們使用count()函式來計算玩家數；玩家使用「角色id」唯一辨識，一個「角色id」對應一位玩家，因此我們對「角色id」進行計數：
1 count(角色id)
由於玩家在一天中可以多次登入遊戲，登入日誌中會存在重復的「角色id」，為了計算不重復玩家數，我們還需要使用distinct子句去重「角色id」，即計算不重復玩家數為：
1 count(distinct 角色id) as 日活躍玩家數
將以上分析按SQL編寫規範組合成完整的SQL語句即可計算出日活躍玩家數。
完整的SQL的書寫方法：
1 select count(distinct 角色id) as 日活躍玩家數
2 from 登入日誌
3 where 日期 = '2022-08-13';
查詢結果如下：
2.次日留存率
因為次日留存率=次日仍登入的活躍玩家數/當日活躍玩家總數。
所以開服首日的次日留存率=開服次日（2022-08-14）仍登入的活躍玩家數/開服首日的活躍玩家總數。
開服首日的活躍玩家總數即開服首日遊戲的DAU，在前面我們已經計算得出，現在我們來計算開服次日仍登入的活躍玩家數。
開服次日仍登入的活躍玩家數表示：開服首日登入過且在開服次日仍然登入的不重復玩家數。這裏存在多個篩選條件：
1）從「登入日誌」中篩選出開服次日（2022-08-14）登入的玩家：
1 select 角色id
2 from 登入日誌
3 where 日期 = '2022-08-14';
2）並且玩家屬於開服首日（2022-08-13）登入過的玩家：
1 and 角色id in (2022-08-13登入過的使用者id)
2022-08-13登入過的玩家可以用以下SQL語句篩選出：
1 select 角色id
2 from 登入日誌
3 where 日期 = '2022-08-13';
因此，從「登入日誌」篩選開服次日仍登入的玩家的語句為：
1 select 角色id
2 from 登入日誌
3 where 日期 = '2022-08-14' and 角色id in (
4 select 角色id
5 from 登入日誌
6 where 日期 = '2022-08-13');
篩選了玩家後就可以計算不重復玩家數了，計算不重復玩家數使用count(distinct 角色id)。
因此，計算開服次日仍登入的活躍玩家數的完整SQL語句的書寫方法為：
1 select count(distinct 角色id) as 次日仍登入的活躍玩家數
2 from 登入日誌
3 where 日期 = '2022-08-14' and 角色id in (
4 select 角色id
5 from 登入日誌
6 where 日期 = '2022-08-13');
查詢結果如下：
現在，們在前面結果的基礎上計算開服首日的次日留存率。
計算開服首日（2022-08-13）的活躍玩家總數的SQL的書寫方法如下：
1 select count(distinct 角色id)
2 from 登入日誌
3 where 日期 = '2022-08-13';
根據次日留存率的計算方法可知，計算次日仍登入的活躍玩家數的count(distinct 角色id)/計算首日登入的活躍使用者數的count(distinct 角色id)即為次日留存率。
因此，可以將計算開服首日的活躍玩家總數的SQL語句帶入計算開服次日仍登入的活躍玩家數的SQL語句中，進行除法運算。
完整SQL的書寫方法:
1 select count(distinct 角色id)/(select count(distinct 角色id) from 登入日誌 where 日期 = '2022-08-13') as 次日留存率
2 from 登入日誌
3 where 日期 = '2022-08-14' and 角色id in (
4 select 角色id
5 from 登入日誌
6 where 日期 = '2022-08-13');
查詢結果如下：
即開服首日（2022-08-13）的次日留存率為75%。
案例數據下載途徑：
