2021-02-22

        with 
        create_player as( 
        select player_id, max(timestamp) as timestamp from `mafia1_ods.game_log_create_player` 
        where timestamp >= '2018-01-01' 
        group by player_id), 
         
        rank as(      
         SELECT CAST(FLOOR(group_id/5) as int64) as group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM      
            (select  MOD(CAST(CONCAT('0x', SUBSTR(TO_HEX(MD5(CONCAT('5rv6e', CAST(player_id AS STRING)))),0, 8)) AS INT64), 100) as group_id,  pay_dollar as pay_dollar FROM `heidao-market.mafia1_pf.v_paid_order`      
        WHERE pay_time >= '2021-02-03 12:36:00' and pay_time <'2021-12-21 09:00:00'  and player_id not in (select player_id from mafia1_pf.v_internal_player) 
        #last_timestamp = None 
        #and player_id in (select player_id from create_player where timestamp between '2020-06-12 0:00:00' and  '2020-07-01') 
         )         
        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 desc 
        order by group_id desc