By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE activity
(player_id integer,
device_id integer,
event_date date,
games_played integer);
INSERT INTO activity
(player_id,
device_id,
event_date,
games_played)
VALUES
(1, 2, '2016-03-01', 5),
(1, 2, '2016-03-02', 6),
(2, 3, '2017-06-25', 1),
(3, 1, '2016-03-02', 0),
(3, 4, '2018-07-03', 5);
5 rows affected
SELECT round(count(DISTINCT a2.player_id) * 1.0
/
count(DISTINCT a1.player_id) * 1.0,
2) AS fraction
FROM activity a1
LEFT JOIN activity a2
ON a1.player_id = a2.player_id
AND a1.event_date = dateadd(day, -1, a2.event_date);
| fraction |
|---|
| 0.3300000000000 |
Warning: Null value is eliminated by an aggregate or other SET operation.