sql 提取　用户数据

   with rank as(     
   SELECT (CASE WHEN group_id < 90 then 'A' else 'B' END) as group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM     
   (select  MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('so69p', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) as group_id,  pay_dollar FROM `heidao-market.mafia1_pf.v_paid_or
  der`     
   WHERE pay_time >='2020-02-05 09:50:00' and pay_time <='2020-12-20 05:40:00' and player_id not in (select player_id from mafia1_pf.v_internal_player))     
   # and player_id in (select playe_id FROM mafia1.create_player where timestamp >='2019-07-01'))     
   #where pay_dollar >= 2 and pay_dollar <= 7     
   group by group_id     
   order by pay_dollars)     
    
   select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)     
   Order by ratio    
                  

with  
       f as (
   select * from (
   SELECT   player_id  , DATE(timestamp ) as dt ,ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY timestamp DESC) AS rn, FROM `heidao-market.mafia1_ods.game_log_login` 
   WHERE DATE(timestamp) >=  '2019-12-09' 　and DATE(timestamp) <='2020-01-09')
   where rn=1),
    
    A as (SELECT kingdom_id,player_id,timestamp,uid FROM `heidao-market.mafia1_ods.game_log_create_player` WHERE player_id in (select player_id from f ) 
    and timestamp >'2014-01-01'),
           
      B as (select device_id, uid ,attribute_lang        from `mafia1_pf.v_game_user` where create_time >'2014-01-01'),
      
    
      myevent as (  select A.uid,B.device_id as de_id , B.attribute_lang as language,
                  A.timestamp as created_time, A.kingdom_id,A.player_id from B  inner join  A on B.uid=A.uid ),
      
      C as (select * from `mafia1_pf.v_device_list`  where create_time >'2014-01-01' ),
    
     D as (select 
    --distinct C.time as device_time,
    date(myevent.created_time)  as created_date,
    myevent.player_id,
    myevent.language ,
    C.country_code,
    C.source,
    C.device_type,
    C.os_type,
    C.system_lang,kingdom_id 
    from C
    inner join   myevent on C.device_id=myevent.de_id)
    
    select distinct player_id,created_date,language	,country_code,source,device_type,os_type,system_lang from D