SalehUser Distribution Based on Swap Volume
    Updated 2024-10-08
    with lst_all as (
    select
    block_timestamp::date as date
    ,tx_id
    ,from_address as wallet
    ,split(pool_name,'-')[0] as Pool
    ,iff( FROM_AMOUNT_USD>=TO_AMOUNT_USD,FROM_AMOUNT_USD,TO_AMOUNT_USD) as amount_usd
    from thorchain.defi.fact_swaps
    -- where block_timestamp::date>='2024-01-01'
    where AFFILIATE_ADDRESS in('T','t','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk')
    and _TX_TYPE='swap'
    )
    ,lst_all_raw as (
    SELECT
    wallet
    ,sum(amount_usd) as "Amount USD"
    FROM lst_all
    group by 1
    )
    select
    case
    when ("Amount USD"!=0) and "Amount USD"<=10 then '[less than $10]'
    when "Amount USD">=11 and "Amount USD"<=100 then '[$11-$100]'
    when "Amount USD">=101 and "Amount USD"<=500 then '[$101-$500]'
    when "Amount USD">=501 and "Amount USD"<=1000 then '[$501-$1,000]'
    when "Amount USD">=1001 and "Amount USD"<=5000 then '[$1,001-$5,000]'
    when "Amount USD">=5001 and "Amount USD"<=10000 then '[$5,001-$10,000]'
    when "Amount USD">=10001 then '[More than $10,000]'
    else 'Other'
    end as "Volume($)-Based Distribution"
    ,count(DISTINCT wallet ) as Traders
    from lst_all_raw
    group by 1
    order by Traders desc

    QueryRunArchived: QueryRun has been archived