MasiShare of Traders
    Updated 2024-10-10
    with tb1 as ( select block_timestamp,
    tx_id,
    blockchain,
    pool_name,
    ifnull(from_address,native_to_address) as trader,
    from_asset,
    to_asset,
    case when from_amount_usd >= to_amount_usd then to_amount_usd else from_amount_usd end as amount1,
    case when from_amount_usd is null then to_amount_usd else from_amount_usd end as amount2,
    case when amount1 is null then amount2 else amount1 end as amount
    from thorchain.defi.fact_swaps
    where affiliate_address in ('td','ti','tr','te')
    )
    ,
    tb2 as ( select min(block_timestamp) as days,
    trader
    from tb1
    group by 2
    having days::date >= current_date - 60 )

    select trunc(block_timestamp,'day') as day,
    case when trader in (select trader from tb2) then 'New Traders' else 'Old Traders' end as status,
    sum(amount) as volume
    from tb1
    where block_timestamp::date >= current_date -30
    group by 1,2



    Last run: about 2 months ago
    DAY
    STATUS
    VOLUME
    1
    2024-12-23 00:00:00.000Old Traders1650609.77814956
    2
    2024-12-28 00:00:00.000New Traders4732970.68622276
    3
    2024-12-11 00:00:00.000New Traders9001583.22396627
    4
    2024-12-03 00:00:00.000New Traders12086205.2257861
    5
    2024-12-14 00:00:00.000Old Traders7091104.70828228
    6
    2024-12-17 00:00:00.000New Traders7895135.78911777
    7
    2024-12-20 00:00:00.000Old Traders4993068.46220196
    8
    2024-12-25 00:00:00.000Old Traders1291431.64929257
    9
    2024-12-24 00:00:00.000Old Traders1382381.63077559
    10
    2024-12-15 00:00:00.000New Traders4959566.07119977
    11
    2024-12-10 00:00:00.000New Traders12687919.402788
    12
    2024-12-17 00:00:00.000Old Traders2213163.72039496
    13
    2024-12-30 00:00:00.000New Traders3388251.59979384
    14
    2024-12-12 00:00:00.000Old Traders1890913.64361502
    15
    2024-12-23 00:00:00.000New Traders7785368.03322302
    16
    2024-12-27 00:00:00.000Old Traders1409931.36823557
    17
    2025-01-02 00:00:00.000Old Traders932523.550203161
    18
    2024-12-10 00:00:00.000Old Traders2167711.37756962
    19
    2024-12-15 00:00:00.000Old Traders779515.671534505
    20
    2024-12-08 00:00:00.000New Traders4409775.53136842
    62
    4KB
    3s