sql - 生成一份报告，显示首次登录 7 天后未再次登录的用户百分比

Question

我正在尝试生成一份报告，显示首次登录 7 天后未再次登录的用户百分比。目前我可以编写一个 SQL 脚本来显示 2017 年 1 月 1 日至 2017 年 2 月 1 日之间登录的唯一用户数。但是如何找到 7 天后未再次登录的用户数？谢谢

select
  count ( distinct a.user_id) as unique_user_ids_logins_in_month,
  to_char(first_hit_at::date,'dd-mm-yyyy') as date
from
  stg_marketing.ga_sessions a
where
  a.first_hit_at >('2017-01-01 00:00:00.000')
  and a.first_hit_at <('2017-02-01 00:00:00.000')
  and user_login_state = 'true' 
  and last_hit_at::date > first_hit_at::date 
group by 2
order by 2 asc


 Unique_user_logins    Date
  97                 01-01-2017
  96                 02-01-2017
  62                 03-01-2017
  61                 04-01-2017
  69                 05-01-2017
  65                 06-01-2017
  75                 07-01-2017
  82                 08-01-2017

Answer 1

我不知道你到底需要什么输出，但这是你可以找到唯一用户的方法，这些用户只有一次登录，或者第一次和第二次登录之间的间隔超过 7 天:

with  t(user_id, login_dt) as(
    select 1, '2017-01-01 02:00:00'::timestamp union all
    select 1, '2017-01-05 02:00:00'::timestamp union all
    select 2, '2017-01-01 02:00:00'::timestamp union all
    select 2, '2017-01-09 02:00:00'::timestamp union all
    select 3, '2017-01-01 02:00:00'::timestamp union all
    select 3, '2017-01-05 02:00:00'::timestamp union all
    select 4, '2017-01-01 02:00:00'::timestamp union all
    select 4, '2017-01-10 02:00:00'::timestamp union all
    select 4, '2017-01-11 02:00:00'::timestamp union all
    select 22, '2017-01-10 02:00:00'::timestamp union all
    select 22, '2017-01-10 02:00:04'::timestamp  

)


select user_id  from (
    select user_id, login_dt::date, row_number() over(partition by user_id  order by login_dt) as rn
    from t
    where
    login_dt >= '2017-01-01' and login_dt < '2017-02-01'
) t
where
rn < 3
group by user_id
having
count(login_dt) = 1
or
min(login_dt) + 7 < max(login_dt)

演示:http://rextester.com/RKEHTZ93773