ll

with pld as (SELECT  player_id FROM `heidao-market.mafia1_pf.v_player_attrs` WHERE last_act_time > "2020-08-28"  and player_name ='西朗骨頭'),

locat as (SELECT * FROM `heidao-market.mafia1_ods.game_log_move_castle` WHERE timestamp > "2020-09-15" ),

js1 as (select  locat.* from locat    join   pld  on  locat.player_id =pld.player_id  order by timestamp  desc),

js2 as (
       SELECT * FROM `heidao-market.mafia1_pf.v_player_attrs` where last_act_time >'2020-09-20')
       
       
select js1.* ,js2.pay_dollar  from js1 join js2 on js1.player_id=js2.player_id