создать таблицу удержания на основе сыгранных игр

Ситуация:

У меня есть таблица с тремя столбцами:

  • свидание
  • игра
  • ID пользователя

Из этой таблицы мне нужно создать таблицу хранения, которая в конечном итоге будет выглядеть так:

Вывод:

+------------+-------------+------+-------------+------+------+-------+
|    date    |    game     | day0 |    day1     | day3 | day7 | day10 |
+------------+-------------+------+-------------+------+------+-------+
| 2019-01-01 | fifa        |  100 | % of day  0 |      |      |       |
| 2019-01-01 | nba         |  100 |             |      |      |       |
| 2019-01-01 | battlefield |  100 |             |      |      |       |
| 2019-01-02 | fifa        |  100 |             |      |      |       |
| 2019-01-02 | battlefield |  100 |             |      |      |       |
| 2019-01-03 | fifa        |  100 |             |      |      |       |
| 2019-01-03 | nba         |  100 |             |      |      |       |
| ...        |             |      |             |      |      |       |
+------------+-------------+------+-------------+------+------+-------+

day0 равно 100, если хотя бы один человек играл в игру

day1 — это доля отдельных пользователей, которые вернулись через 1 или 2 дня.

day3 — это доля отдельных пользователей, которые вернулись через 3–6 дней.

и так далее...

если вы нанесете числа, это должно дать вам нелинейную убывающую кривую.

Логика:

(см. примеры данных ниже)

Скелет, который я построил до сих пор, выглядит так:

;WITH 
    baseDate AS (
        SELECT
         'target_date' AS [key_name]
        ,CAST('2019-01-01' AS date) AS [key_value]
        ),
durationDate AS (
    SELECT DATEADD(DAY,0,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
    UNION ALL
    SELECT DATEADD(DAY,1,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
    UNION ALL
    SELECT DATEADD(DAY,2,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
    UNION ALL
    SELECT DATEADD(DAY,3,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
    UNION ALL
    SELECT DATEADD(DAY,4,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
    UNION ALL
    SELECT DATEADD(DAY,5,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
    UNION ALL
    SELECT DATEADD(DAY,6,key_value) AS target_date FROM baseDate WHERE [key_name] = 'target_date'
)
    select 
        dd.target_date
        , play.game 
        , play.[count]/play.count *100 as day0
    from durationDate as dd
    left join (
        select t1.date, t1.game, COUNT(distinct t1.user_id) as [count]
        from #t1 t1
        group by t1.date, t1.game
        ) as play on dd.target_date=play.[date]

Вероятно, мне не хватает другой базовой таблицы для столбцов day1, day3, day7, например:

durationDateColumn AS (
        SELECT 
              DATEADD(DAY,0,key_value) AS day1
            , DATEADD(DAY,1,key_value) AS day2
            , DATEADD(DAY,2,key_value) AS day3
            , DATEADD(DAY,3,key_value) AS day4
            , DATEADD(DAY,4,key_value) AS day5
            , DATEADD(DAY,5,key_value) AS day6
            , DATEADD(DAY,6,key_value) AS day7
        FROM baseDate
        WHERE [key_name] = 'target_date'
    )

если нет более эффективного способа сделать это.

Пример данных:

drop table if exists #t1
create table #t1 ([date] date, [game] varchar (20),[user_id] bigint)
insert into #t1 values 
('2019-01-01', 'fifa',11),
('2019-01-01', 'fifa',12),
('2019-01-01', 'fifa',13),
('2019-01-01', 'fifa',14),
('2019-01-02', 'fifa',12),
('2019-01-02', 'fifa',13),
('2019-01-02', 'fifa',14),
('2019-01-04', 'fifa',12),
('2019-01-04', 'fifa',13),
('2019-01-08', 'fifa',13),
('2019-01-01', 'nba',11),
('2019-01-01', 'nba',13),
('2019-01-01', 'nba',14),
('2019-01-02', 'nba',13),
('2019-01-02', 'nba',14),
('2019-01-04', 'nba',13),
('2019-01-08', 'nba',13)

sql sql-server retention
person Roger Steinberg    schedule 07.08.2019    source источник
comment
Не уверен, как кривая уменьшается, если предположить, что на 6-й день вернулось больше пользователей, чем на 2-й день.   -  person Serg    schedule 08.08.2019
comment
обычно так выглядит удержание, мои образцы данных не являются репрезентативными, я их изменю. благодарю вас   -  person Roger Steinberg    schedule 08.08.2019

Ответы (1)


arrow_upward
1
arrow_downward

Соедините данные о дате и следующих датах, рассчитайте количество дней и количество пользователей. Сводка по количеству дней с использованием условной агрегации.

create table #t1 ([date] date, [game] varchar (20),[user_id] bigint);

insert into #t1 values 
('2019-01-01', 'fifa',11),
('2019-01-01', 'fifa',12),
('2019-01-01', 'fifa',13),
('2019-01-01', 'fifa',14),
('2019-01-02', 'fifa',12),
('2019-01-02', 'fifa',13),
('2019-01-02', 'fifa',14),
('2019-01-04', 'fifa',12),
('2019-01-04', 'fifa',13),
('2019-01-08', 'fifa',13),
('2019-01-01', 'nba',11),
('2019-01-01', 'nba',13),
('2019-01-01', 'nba',14),
('2019-01-02', 'nba',13),
('2019-01-02', 'nba',14),
('2019-01-04', 'nba',13),
('2019-01-08', 'nba',13);

select [date], [game] 
  , 100.0 day0
  , cast(100.0 * (0.0 + sum(case ret when 1 then n else 0 end)) / sum(case ret when 0 then n end) as decimal(5,2)) day1
  , cast(100.0 * (0.0 + sum(case ret when 2 then n else 0 end)) / sum(case ret when 0 then n end) as decimal(5,2)) day2
  , cast(100.0 * (0.0 + sum(case ret when 3 then n else 0 end)) / sum(case ret when 0 then n end) as decimal(5,2)) day3
  , cast(100.0 * (0.0 + sum(case ret when 4 then n else 0 end)) / sum(case ret when 0 then n end) as decimal(5,2)) day4
  -- .. more days
from (
    select t1.[date], t1.[game], Datediff(dd, t1.[Date], t2.[Date]) ret, count(distinct t1.[user_id]) n
    from #t1 t1
    left join #t1 t2
        on t1.game = t2.game and t1.user_id = t2.user_id and t1.date <= t2.date
    group by t1.[date], t1.[game], Datediff(dd, t1.[Date], t2.[Date])
) evt
group by [date], [game]
order by [date], [game];

Выход

date    game    day0    day1    day2    day3    day4
2019-01-01  fifa    100.0   75.00   0.00    50.00   0.00
2019-01-01  nba     100.0   66.67   0.00    33.33   0.00
2019-01-02  fifa    100.0   0.00    66.67   0.00    0.00
2019-01-02  nba     100.0   0.00    50.00   0.00    0.00
2019-01-04  fifa    100.0   0.00    0.00    0.00    50.00
2019-01-04  nba     100.0   0.00    0.00    0.00    100.00
2019-01-08  fifa    100.0   0.00    0.00    0.00    0.00
2019-01-08  nba     100.0   0.00    0.00    0.00    0.00
person Serg    schedule 08.08.2019