SHARE
TWEET

Untitled

a guest Sep 16th, 2019 106 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH digit as (select 0 as d                                                                                        
  2.    union all select 1                                                                                                        
  3.    union all select 2                                                                                              
  4.    union all select 3                                                                                              
  5.    union all select 4                                                                                                        
  6.    union all select 5                                                                                              
  7.    union all select 6                                                                                              
  8.    union all select 7                                                                                                        
  9.    union all select 8                                                                                              
  10.    union all select 9),                                                                                            
  11. HUNDRED as (
  12.   select 1 + a.d + (10 * b.d) as num                                                                  
  13.   from digit a                                                                                        
  14.   cross join digit b                                                                          
  15.   order by 1),                                                                                        
  16. HUNDRED_DAYS AS (
  17.   SELECT date_add(current_date, INTERVAL -num day) AS dt                                                
  18.   FROM HUNDRED),                                                                                
  19. CLEAN_USER_TABLE AS (
  20.   SELECT
  21.     FILTERED_USER_TABLE.UDID,                                                          
  22.     FILTERED_USER_TABLE.game,                                                          
  23.     FILTERED_USER_TABLE.install_date,                                                  
  24.     GEO_BUCKET.geo,                                                                    
  25.     CASE                                                                                
  26.       WHEN HARDWARE.device_type IS NULL THEN 'other'                                                              
  27.       ELSE HARDWARE.device_type                                                        
  28.       END AS device_type,                                                            
  29.     CASE                                                                                
  30.       WHEN INSTALL_CHANNEL.channel IS NULL THEN 'Organic'                                                            
  31.       ELSE 'Paid'                                                                      
  32.       END AS channel                                                                  
  33.   FROM
  34.     physical.users AS FILTERED_USER_TABLE                                                
  35.     LEFT JOIN (
  36.       SELECT
  37.       -- do we need distinct since each udid should only have one channel anyways?
  38.         DISTINCT game, channel, udid                                      
  39.       FROM
  40.         physical.channelclaims)
  41.     INSTALL_CHANNEL                            
  42.     ON (
  43.       INSTALL_CHANNEL.game = FILTERED_USER_TABLE.game AND                          
  44.       FILTERED_USER_TABLE.udid = INSTALL_CHANNEL.udid
  45.     )                              
  46.     LEFT JOIN
  47.     public.hardwareequiv HARDWARE                                            
  48.     ON (
  49.       FILTERED_USER_TABLE.hw_ver = HARDWARE.hw_ver
  50.     )                                
  51.     LEFT JOIN (
  52.       SELECT
  53.         geo_bucket AS geo, ip_country                                    
  54.       FROM
  55.         public.geo_buckets)
  56.     GEO_BUCKET                                      
  57.     ON (
  58.       FILTERED_USER_TABLE.ip_country = GEO_BUCKET.ip_country
  59.       )
  60.     ),
  61. ACTIVE_SESSION AS (
  62.   SELECT
  63.     CLEAN_USER_TABLE.game,                                                                
  64.     sess.date,                                                              
  65.     CLEAN_USER_TABLE.geo,                                                                
  66.     CLEAN_USER_TABLE.channel,                                                            
  67.     CLEAN_USER_TABLE.device_type,                                                        
  68.     CASE                                                                                  
  69.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) = 0 THEN '(0) 0'
  70.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) = 1 THEN '(1) 1'                                                                
  71.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 2 AND 6 THEN '(2) 2-6'
  72.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 7 AND 14 THEN '(3) 7-14'
  73.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 15 AND 30 THEN '(4) 15-30'
  74.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 31 AND 60 THEN '(5) 31-60'
  75.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 61 AND 90 THEN '(6) 61-90'
  76.       WHEN DATE_DIFF(sess.date, CLEAN_USER_TABLE.install_date, DAY) > 90 THEN '(7) 90 plus'                                                          
  77.       END AS cohortbucket,                              
  78.     COUNT(DISTINCT sess.udid) AS daus                                        
  79.   FROM
  80.     physical.sessions as sess                                                                    
  81.     LEFT JOIN CLEAN_USER_TABLE                                                            
  82.     ON
  83.       sess.game = CLEAN_USER_TABLE.game AND                              
  84.       sess.udid = CLEAN_USER_TABLE.udid                                  
  85.   WHERE
  86.     sess.date BETWEEN date_sub(current_date, INTERVAL 100 day) AND date_sub(current_date, INTERVAL 1 day)                                    
  87.   GROUP BY
  88.     CLEAN_USER_TABLE.game,                                                              
  89.     sess.date,                                                            
  90.     CLEAN_USER_TABLE.geo,                                                              
  91.     CLEAN_USER_TABLE.channel,                                                          
  92.     CLEAN_USER_TABLE.device_type,                                                      
  93.     cohortbucket
  94. ),                                                                    
  95. IAPS AS (
  96.   SELECT
  97.     CLEAN_USER_TABLE.game,                                                                          
  98.     iaps.date,                                                                            
  99.     CLEAN_USER_TABLE.geo,                                                                          
  100.     CLEAN_USER_TABLE.channel,                                                                      
  101.     CLEAN_USER_TABLE.device_type,                                                                  
  102.     CASE                                                                                            
  103.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) = 0 THEN '(0) 0'
  104.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) = 1 THEN '(1) 1'
  105.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 2 AND 6 THEN '(2) 2-6'
  106.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 7 AND 14 THEN '(3) 7-14'
  107.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 15 AND 30 THEN '(4) 15-30'
  108.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 31 AND 60 THEN '(5) 31-60'
  109.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) BETWEEN 61 AND 90 THEN '(6) 61-90'
  110.       WHEN DATE_DIFF(iaps.date, CLEAN_USER_TABLE.install_date, DAY) > 90 THEN '(7) 90 plus'
  111.       END
  112.       AS cohortbucket,                                            
  113.     sum(rev)/100 AS Revenue,                                                  
  114.     COUNT(DISTINCT iaps.udid) AS payers                                                    
  115.   FROM
  116.     physical.iaps as iaps                                                                              
  117.     LEFT JOIN
  118.     CLEAN_USER_TABLE                                                                      
  119.     ON
  120.       iaps.game = CLEAN_USER_TABLE.game AND                                            
  121.       iaps.udid = CLEAN_USER_TABLE.udid                                                
  122.   WHERE
  123.     iaps.date BETWEEN date_sub(current_date, INTERVAL 100 day) AND date_sub(current_date, INTERVAL 1 day)
  124.   GROUP BY
  125.     CLEAN_USER_TABLE.game,                                                                        
  126.     iaps.date,                                                                          
  127.     CLEAN_USER_TABLE.geo,                                                                        
  128.     CLEAN_USER_TABLE.channel,                                                                    
  129.     CLEAN_USER_TABLE.device_type,                                                                
  130.     cohortbucket
  131. ),                                                                                
  132. INSTALL_COHORT AS (
  133.   SELECT
  134.     INSTALL_COUNT.game,                                                                  
  135.     HUNDRED_DAYS.dt AS date,                                                              
  136.     INSTALL_COUNT.geo,                                                                    
  137.     INSTALL_COUNT.channel,                                                                
  138.     INSTALL_COUNT.device_type,                                                            
  139.     CASE                                                                                  
  140.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) = 0 THEN '(0) 0'                                                                
  141.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) = 1 THEN '(1) 1'                                                                
  142.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 2 AND 6 THEN '(2) 2-6'
  143.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 7 AND 14 THEN '(3) 7-14'
  144.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 15 AND 30 THEN '(4) 15-30'
  145.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 31 AND 60 THEN '(5) 31-60'
  146.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) BETWEEN 61 AND 90 THEN '(6) 61-90'
  147.       WHEN DATE_DIFF(HUNDRED_DAYS.dt, INSTALL_COUNT.install_dt, day) > 90 THEN '(7) 90 plus'                                                          
  148.       END
  149.       AS cohortbucket,                                                      
  150.     SUM(installs) AS cohort_install                                                    
  151.   FROM (
  152.     SELECT
  153.       CLEAN_USER_TABLE.game,                                                          
  154.       CLEAN_USER_TABLE.install_date AS install_dt,                                    
  155.       CLEAN_USER_TABLE.geo,                                                          
  156.       CLEAN_USER_TABLE.channel,                                                      
  157.       CLEAN_USER_TABLE.device_type,                                                  
  158.       COUNT(distinct udid) as installs                                      
  159.     FROM
  160.       CLEAN_USER_TABLE                                                                  
  161.     GROUP BY
  162.       game, install_dt, geo, channel, device_type
  163.   ) AS INSTALL_COUNT                                                
  164.   CROSS JOIN
  165.   HUNDRED_DAYS                                                              
  166.   GROUP BY
  167.     INSTALL_COUNT.game,                                                                
  168.     HUNDRED_DAYS.dt,                                                                    
  169.     INSTALL_COUNT.geo,                                                                  
  170.     INSTALL_COUNT.channel,                                                              
  171.     INSTALL_COUNT.device_type,                                                          
  172.     cohortbucket
  173. )                                                                      
  174.                                                                                                                    
  175. SELECT SESS_IAP.game AS game,                                                                                        
  176.       SESS_IAP.date AS date,                                                                                        
  177.       SESS_IAP.geo AS geo,                                                                                          
  178.       SESS_IAP.channel AS channel,                                                                                  
  179.       SESS_IAP.device_type AS device_type,                                                                          
  180.       SESS_IAP.cohortbucket AS cohortbuckets,                                                                      
  181.       INSTALL_COHORT.cohort_install AS installs,                                                                    
  182.       SESS_IAP.daus    AS daus,                                                                                    
  183.       SESS_IAP.payers  AS payers,                                                                                  
  184.       SESS_IAP.Revenue AS Revenue                                                                                  
  185. FROM (
  186.   SELECT
  187.     ACTIVE_SESSION.game,
  188.     ACTIVE_SESSION.date,
  189.     ACTIVE_SESSION.geo,                                          
  190.     ACTIVE_SESSION.channel,                                                                              
  191.     ACTIVE_SESSION.device_type,                                                                          
  192.     ACTIVE_SESSION.cohortbucket,
  193.     SUM(ACTIVE_SESSION.daus) AS daus,                                        
  194.     SUM(IAPS.payers) AS payers,
  195.     SUM(IAPS.Revenue) AS Revenue                                                
  196.   FROM
  197.     ACTIVE_SESSION
  198.     FULL OUTER JOIN
  199.     IAPS
  200.     ON (
  201.       ACTIVE_SESSION.game = IAPS.game AND                                                
  202.       ACTIVE_SESSION.date = IAPS.date AND                                                
  203.       ACTIVE_SESSION.geo = IAPS.geo AND                                                  
  204.       ACTIVE_SESSION.channel = IAPS.channel AND                                          
  205.       ACTIVE_SESSION.device_type = IAPS.device_type AND                                  
  206.       ACTIVE_SESSION.cohortbucket = IAPS.cohortbucket
  207.     )                                    
  208.     GROUP BY
  209.       ACTIVE_SESSION.game,
  210.       ACTIVE_SESSION.date,
  211.       ACTIVE_SESSION.geo,                                        
  212.       ACTIVE_SESSION.channel,                                                                              
  213.       ACTIVE_SESSION.device_type,                                                                          
  214.       ACTIVE_SESSION.cohortbucket
  215. ) AS SESS_IAP                                                              
  216. LEFT JOIN
  217. INSTALL_COHORT
  218. ON (
  219.   SESS_IAP.game = INSTALL_COHORT.game AND                                          
  220.   SESS_IAP.date = INSTALL_COHORT.date AND                                          
  221.   SESS_IAP.geo = INSTALL_COHORT.geo AND                                            
  222.   SESS_IAP.channel = INSTALL_COHORT.channel AND                                    
  223.   SESS_IAP.device_type =INSTALL_COHORT.device_type AND                            
  224.   SESS_IAP.cohortbucket = INSTALL_COHORT.cohortbucket
  225. )
  226. ORDER BY game desc
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top