messariThorchain weekly swap volume by affiliate
    Updated 2025-04-08
    -- forked from Rayyyk / Weekly Stats 7 @ https://flipsidecrypto.xyz/Rayyyk/q/8UQPX3EjXesx/weekly-stats-7

    select date_trunc('week', block_timestamp) as week,
    case
    when affiliate_address in ('t', 'T', 'thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk') then 'THORSwap'
    when affiliate_address in ('wr', 'thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8') then 'THORWallet'
    when affiliate_address = 'tl' then 'TS Ledger'
    when affiliate_address = 'cb' then 'CoinBot'
    when affiliate_address = 'dx' then 'Asgardex'
    when affiliate_address = 'ss' then 'ShapeShift'
    when affiliate_address = 'xdf' then 'xDEFI'
    when affiliate_address = 'rg' then 'Rango'
    when affiliate_address = 'ej' then 'Edge Wallet'
    when affiliate_address = 'ds' then 'DefiSpot'
    when affiliate_address = 'lends' then 'Lends'
    when affiliate_address = 'decentralfi' then 'DecentralFi'
    when affiliate_address in ('ti', 'te', 'tr', 'td') then 'TrustWallet'
    when affiliate_address = 'lifi' then 'LiFi'
    when affiliate_address = 'oky' then 'OneKey Wallet'
    when affiliate_address = 'sy' then 'Symbiosis'
    when affiliate_address = 'vi' then 'Vultisig'
    when affiliate_address = 'cakewallet' then 'Cake Wallet'
    when affiliate_address = 'okw' then 'OKX Wallet'
    when affiliate_address is null then 'No Affiliate'
    --else 'Other'
    end as "Affiliates",
    sum(from_amount_usd) as usd_volume,
    sum(usd_volume) over (partition by "Affiliates" order by week) as cumu_usd_volume
    from thorchain.defi.fact_swaps
    where block_timestamp < date_trunc('week', current_date)
    and block_timestamp >= '2023-06-01'
    group by 1,2
    having "Affiliates" != 'No Affiliate'
    order by 3 desc


    QueryRunArchived: QueryRun has been archived