drop table if EXISTS Activity;
create table Activity(
player_id int,
device_id int,
event_date date,
games_played int
);
insert into Activity values(1, 2, '2016-03-01', 5);
insert into Activity values(1, 2, '2016-03-02', 6);
insert into Activity values(2, 3, '2017-06-25', 1);
insert into Activity values(3, 1, '2016-03-01', 0);
insert into Activity values(3, 4, '2016-07-03', 5);
select a1.install_dt,
count(1) installs,
round(count(a2.event_date)/count(*),2) Day1_retention
from(
-- 算出每個人的安裝日期
select player_id,min(event_date) install_dt
from Activity
group by player_id
) a1
left join Activity a2
-- 根據條件安裝日期后一天登錄為重新登錄
on a1.player_id = a2.player_id and datediff(a2.event_date,a1.install_dt)=1
group by a1.install_dt
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/206301.html
標籤：其他