ENVxKt5N

1DROP TEMPORARY TABLE IF EXISTS cheaters
2CREATE TEMPORARY TABLE cheaters AS
3SELECT
4        profile_id
5    FROM cheater_marks_dist FINAL
6    WHERE action = 'marked'
7    UNION ALL
8    SELECT
9        profile_id
10    FROM profile_dynamics_dist
11    PREWHERE occurrence_type = 'QAStatus'
12    GROUP BY profile_id
13    HAVING argMax(occurrence_value, created_on) = '1'
14
15
16SELECT
17    'gross' AS column,
18    segment,
19    CAST(sumIf(price_usd, _day = today()) as Float) AS value,
20    CAST(sumIf(price_usd, _day != today()) as Float) AS last
21FROM (
22    WITH
23        upperUTF8(environment) AS _segment
24    SELECT
25        profile_id,
26        argMax(_segment, created_on) AS segment,
27        argMax(price_usd, created_on) AS price_usd,
28        argMax(day, created_on) AS _day
29    FROM purchases_dist
30    PREWHERE day IN (today(), '2021-05-20')
31    WHERE toTime(toDateTime(created_on))
32        BETWEEN toTime(toDateTime(today())) AND toTime(toDateTime(now()))
33    AND is_qa_purchase IN ('False', 'false')
34    AND _segment IN ('ANDROID_GOOGLEPLAY','ARMOR','FBCANVAS','FB_GAMEROOM','HUAWEI','IOS','KONGREGATE','MTPSITE','SAMSUNGGALAXYSTORE','STEAM','VSEIGRY')
35    GROUP BY
36        profile_id,
37        transaction_id,
38        created_on
39)
40WHERE profile_id not in cheaters
41GROUP BY segment
42
43UNION ALL
44
45SELECT
46    'installs' AS column,
47    upperUTF8(platform_id) AS segment,
48    CAST(uniqIf(profile_id, day = today()) as Float) AS value,
49    CAST(uniqIf(profile_id, day != today()) as Float) AS last
50FROM registers_dist
51PREWHERE day IN (today(), '2021-05-20')
52WHERE toTime(toDateTime(created_on))
53BETWEEN toTime(toDateTime(today())) AND toTime(toDateTime(now()))
54AND segment IN ('ANDROID_GOOGLEPLAY','ARMOR','FBCANVAS','FB_GAMEROOM','HUAWEI','IOS','KONGREGATE','MTPSITE','SAMSUNGGALAXYSTORE','STEAM','VSEIGRY')
55AND profile_id not in cheaters
56GROUP BY segment
57
58UNION ALL
59
60SELECT
61    'players' AS column,
62    upperUTF8(platform_id) AS segment,
63    CAST(uniqIf(profile_id, day = today()) as Float) AS value,
64    CAST(uniqIf(profile_id, day != today()) as Float) AS last
65FROM sessions_dist
66PREWHERE day IN (today(), '2021-05-20')
67WHERE toTime(toDateTime(created_on))
68BETWEEN toTime(toDateTime(today())) AND toTime(toDateTime(now()))
69AND event_type LIKE 'start'
70AND segment IN ('ANDROID_GOOGLEPLAY','ARMOR','FBCANVAS','FB_GAMEROOM','HUAWEI','IOS','KONGREGATE','MTPSITE','SAMSUNGGALAXYSTORE','STEAM','VSEIGRY')
71AND profile_id not in cheaters
72GROUP BY segment
73
74UNION ALL
75
76SELECT
77    'new_buyers' AS column,
78    upperUTF8(platform_id) AS segment,
79    CAST(uniqIf(profile_id, day = today()) as Float) AS value,
80    CAST(uniqIf(profile_id, day != today()) as Float) AS last
81FROM registers_dist
82PREWHERE day IN (today(), '2021-05-20')
83WHERE toTime(toDateTime(created_on))
84    BETWEEN toTime(toDateTime(today())) AND toTime(toDateTime(now()))
85AND segment IN ('ANDROID_GOOGLEPLAY','ARMOR','FBCANVAS','FB_GAMEROOM','HUAWEI','IOS','KONGREGATE','MTPSITE','SAMSUNGGALAXYSTORE','STEAM','VSEIGRY')
86AND profile_id not in cheaters
87GROUP BY segment
88