Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- DATE(A.derived_tstamp) AS interval_date
- ,B.league
- ,B.trophy_group
- , B.LTV_group
- , B.payers_segment
- , date_trunc('MONTH',DATE(A.first_install_dt)) AS monthly_cohort
- , B.country
- , B.platform
- , A.purchase_type
- , A.purchase_subtype
- , A.price_point
- , A.template_id
- , A.package
- , A.special_offer_id
- , A.sub_offer_id
- , CASE WHEN A.lt_purchases_amt = 1 THEN 1 ELSE 0 END ftds
- , CASE WHEN A.lt_purchases_amt = 2 THEN 1 ELSE 0 END stds
- , CASE WHEN A.lt_purchases_amt = 3 THEN 1 ELSE 0 END ttds
- , COUNT(A.iap_price) AS Purchases
- , SUM(A.iap_price) AS Revenue
- , COUNT(DISTINCT A.user_id) AS Active_Users
- FROM
- ((SELECT *, DATE(derived_tstamp) interval_Date FROM MATCH_MASTERS.PROD.F_IN_APP_PURCHASE WHERE DATE(derived_tstamp) >= current_date-360) A
- LEFT JOIN
- (SELECT
- user_id
- , interval_date
- , country
- , LANGUAGE
- , platform
- , app_version
- , app_minor_version
- , ltv_group
- , DATE(first_install_dt) AS install_date
- , payers_segment
- , CASE
- WHEN trophies_cnt < 1500 THEN '0-1500'
- WHEN trophies_cnt < 3800 THEN '1500-3800'
- WHEN trophies_cnt < 8000 THEN '3800-8000'
- WHEN trophies_cnt < 17000 THEN '8000-17000'
- WHEN trophies_cnt < 30000 THEN '17000-30000'
- ELSE '30000+'
- END AS trophy_group
- ,CASE
- WHEN trophies_cnt < 800 THEN '800'
- WHEN finish_arena<=13 THEN 'Studios'
- WHEN finish_arena<=22 THEN 'Master_League'
- WHEN finish_arena>=23 THEN 'Legends_League'
- END league
- FROM MATCH_MASTERS.prod.daily_users_from_params WHERE interval_date >= current_date-360) B
- ON A.user_id = B.user_id AND A.interval_date = B.interval_date)
- GROUP BY ALL
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
