--以Stepn官方出入金地址转入转出为例
select
    wallet_address
    ,stat_date
    ,sum(if(tran_type = 'withdraw',amount,0)) as BNB_flow_out_amount
    ,sum(if(tran_type = 'deposit',amount,0)) as BNB_flow_in_amount
from 
(
    SELECT
        date_trunc('day', block_time)  AS stat_date
        , `to` AS wallet_address
        ,'withdraw' AS tran_type
        ,round(SUM(value * pow(10, -18))) AS amount
    FROM`bnb`.`transactions`
    WHERE block_time >= '2022-03-01 00:00'
    AND from = '0x6238872a0bd9f0e19073695532a7ed77ce93c69e'--Stepn官方出入金地址
    AND value > 0
    AND success = 'true'
    group by 
        stat_date
        ,wallet_address
        ,tran_type
    
    union all
    
    SELECT
         date_trunc('day', block_time) AS stat_date
        ,`from` AS wallet_address
        ,'deposit' AS tran_type
        ,round(SUM(value * pow(10, -18))) AS amount
    FROM`bnb`.`transactions`
    WHERE block_time >=  '2022-03-01 00:00'
    AND to = '0x6238872a0bd9f0e19073695532a7ed77ce93c69e'--Stepn官方出入金地址
    AND value > 0
    AND success = 'true'
    group by 
        stat_date
        ,wallet_address
        ,tran_type
) a 
group by 
    wallet_address
    ,stat_date