SQL練習00012

Activity 活動記錄表

（player_id，event_date）是此表的主鍵
這張表顯示了某些遊戲的玩家的活動情況
每一行是一個玩家的記錄，他在某一天使用某個裝置登出之前登入並玩了很多遊戲（可能是 0）

我們將玩家的安裝日期定義為該玩家的第一個登入日。

我們還將某個日期 X 的第 1 天留存時間定義為安裝日期為 X 的玩家的數量，他們在 X 之後的一天重新登入，除以安裝日期為 X 的玩家的數量，四捨五入到小數點後兩位。

編寫一個 SQL 查詢，報告每個安裝日期、當天安裝遊戲的玩家數量和第一天的留存時間。

查詢結果格式如下所示：

玩家 1 和 3 在 2016-03-01 安裝了遊戲，但只有玩家 1 在 2016-03-02 重新登入，所以 2016-03-01 的第一天留存時間是 1/2=0.50
玩家 2 在 2017-06-25 安裝了遊戲，但在 2017-06-26 沒有重新登入，因此 2017-06-25 的第一天留存時間為 0/1=0.00

--建表語句
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-01', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')


--答案1 視窗函式
select 
  install_dt,
  count(distinct player_id) installs,
  round(sum(if(datediff(event_date,install_dt)=1,1,0))/count(distinct player_id),2) Day1_retention 
from 
  (
  select 
    *,
    min(event_date) over(partition by player_id) install_dt
  from 
  Activity
  )
  t1   
group by 
  install_dt