banbannardLargest Streaming Swaps Transactions copy
    Updated 2024-01-30
    -- forked from Largest Streaming Swaps Transactions @ https://flipsidecrypto.xyz/edit/queries/0ef5aeaa-e64e-4f4e-a218-9ecb6582b19e

    with base as (select tx_id,
    date_trunc('week', block_timestamp) as week,
    affiliate_address, affiliate_fee_basis_points,
    split(from_asset, '-')[0] as from_assets,
    case
    when from_assets ilike '%/%' then split(from_assets, '/')[1]
    else split(from_assets, '.')[1]
    end as from_asset_names,
    split(to_asset, '-')[0] as to_assets,
    case
    when to_assets ilike '%/%' then split(to_assets, '/')[1]
    else split(to_assets, '.')[1]
    end as to_asset_names,
    concat(from_asset_names, ' -> ', to_asset_names) as assets,
    case when assets ilike '%RUNE' then 2
    else 1
    end as numbering,
    sum(to_amount_usd) as swap_volume_usd
    from thorchain.defi.fact_swaps
    where block_timestamp >= '2022-01-01'
    and tx_id in (select tx_id from thorchain.defi.fact_swaps_events where memo ilike '%/%/%')
    group by tx_id, week,
    affiliate_address, affiliate_fee_basis_points,
    from_asset, to_asset),

    base2 as (select week,
    tx_id,
    affiliate_address, affiliate_fee_basis_points,
    array_agg(distinct assets) within group (order by assets asc) as swap_direction, --merging 2 sep path to 1
    sum(swap_volume_usd) as swap_volume
    from base
    group by 1,2,3,4),

    base3 as (select week,
    Last run: 11 months agoAuto-refreshes every 3 hours
    WEEK
    Affiliates
    Avg Swap Size [USD]
    1
    2024-01-01 00:00:00.000No Affiliate8505.294991026
    2
    2024-01-08 00:00:00.000No Affiliate9658.068476104
    3
    2023-11-27 00:00:00.000No Affiliate13806.090260638
    4
    2023-10-02 00:00:00.000THORWallet29882.08836039
    5
    2023-10-09 00:00:00.000ShapeShift19702.546805539
    6
    2023-12-18 00:00:00.000Rango14316.779346855
    7
    2023-12-18 00:00:00.000ti3101.362922145
    8
    2023-12-11 00:00:00.000THORWallet12138.930298155
    9
    2023-09-11 00:00:00.000Asgardex6263.728520784
    10
    2023-09-11 00:00:00.000THORSwap18383.939837325
    11
    2024-01-15 00:00:00.000Rango4583.85061263
    12
    2023-11-13 00:00:00.000Rango3234.622712213
    13
    2023-12-04 00:00:00.000TrustWallet2836.676730564
    14
    2023-10-16 00:00:00.000DefiSpot4032.0160356
    15
    2023-08-21 00:00:00.000TS Ledger8518.205913525
    16
    2023-10-23 00:00:00.00012427.678246782
    17
    2023-10-30 00:00:00.000TS Ledger7947.90534145
    18
    2023-09-04 00:00:00.000commission1743.624611691
    19
    2023-11-13 00:00:00.0002816.645948353
    20
    2023-08-14 00:00:00.000Edge Wallet20.836534878
    ...
    334
    17KB
    11s