From 202c53a8a920d8c972cfa439160d77822609186f Mon Sep 17 00:00:00 2001 From: Ant Zucaro Date: Sun, 30 Dec 2012 22:41:11 -0500 Subject: [PATCH 1/1] Add player retention query --- queries/player_retention_3months.sql | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) create mode 100644 queries/player_retention_3months.sql diff --git a/queries/player_retention_3months.sql b/queries/player_retention_3months.sql new file mode 100644 index 0000000..e19ff14 --- /dev/null +++ b/queries/player_retention_3months.sql @@ -0,0 +1,23 @@ +-- how many distinct player_ids play in multiple months +select count(*) +from players +where +player_id in ( + select distinct player_id + from player_game_stats + where create_dt between (current_timestamp at time zone 'UTC' - interval '1 month') + and (current_timestamp at time zone 'UTC') +) +and player_id in ( + select distinct player_id + from player_game_stats + where create_dt between (current_timestamp at time zone 'UTC' - interval '2 month') + and (current_timestamp at time zone 'UTC' - interval '1 month') +) +and player_id in ( + select distinct player_id + from player_game_stats + where create_dt between (current_timestamp at time zone 'UTC' - interval '3 month') + and (current_timestamp at time zone 'UTC' - interval '2 month') +) +; \ No newline at end of file -- 2.39.2