flipside

    test here 2
    pietrekttest here 2
    Updated 2025-04-28

    -- DOUBLE COUNTING
    with init AS ( SELECT
    block_timestamp, tx_id, from_amount_usd,
    affiliate_address,
    array_size(affiliate_addresses_array) as aff_adds_count,
    CASE
    WHEN aff_adds_count >= 2 AND affiliate_address = '-_' THEN affiliate_addresses_array[1]
    ELSE affiliate_address
    END AS fixed_aff_add
    from thorchain.defi.fact_swaps
    ),

    swaps_filtered AS (SELECT min(to_date(block_timestamp)) as day, tx_id, sum(from_amount_usd) as swap_volume_usd,
    fixed_aff_add as aff_add
    FROM init WHERE AFFILIATE_ADDRESS IS NOT NULL AND (tx_id NOT IN (SELECT DISTINCT tx_id FROM thorchain.defi.fact_refund_events))
    group by tx_id, aff_add),

    asgardex_swaps AS (SELECT min(to_date(block_timestamp)) as day, tx_id, sum(from_amount_usd) as swap_volume_usd, max(min_to_amount) as min_to_amount,
    CASE
    WHEN min(affiliate_address) IS NULL THEN 'dx'
    END AS aff_add
    FROM thorchain.defi.fact_swaps group by tx_id),

    asgardex_swaps_filtered AS (SELECT day, tx_id, swap_volume_usd, aff_add from asgardex_swaps WHERE (RIGHT(min_to_amount, 3) = '999')),
    thorswap_swaps_before_aff_address AS (SELECT min(to_date(block_timestamp)) as day, tx_id, sum(from_amount_usd) as swap_volume_usd, max(min_to_amount) as min_to_amount,
    CASE
    WHEN min(affiliate_address) IS NULL THEN 'T'
    END AS aff_add
    FROM thorchain.defi.fact_swaps WHERE (to_date(block_timestamp) < '2022-04-24') group by tx_id),

    thorswap_swaps_before_aff_address_filtered AS (SELECT day, tx_id, swap_volume_usd, aff_add from thorswap_swaps_before_aff_address WHERE RIGHT(min_to_amount, 3) = '111'),
    trust_ios_swap_ids AS (SELECT max(to_date(block_timestamp)) AS day, tx_id FROM thorchain.defi.fact_swaps_events
    WHERE RIGHT(memo,3) like '%::0%' AND memo like '%=:%' AND block_timestamp < '2023-06-14'
    Last run: 23 days ago
    DAY
    LABEL
    SWAP_VOLUME_USD
    CUMULATIVE_SWAP_VOLUME_USD
    WEEKLY_VOLUME_USD
    MONTHLY_VOLUME_USD
    WEEKLY_TOTAL_VOLUME_USD
    MONTHLY_TOTAL_VOLUME_USD
    WEEKLY_PCT_OF_TOTAL
    MONTHLY_PCT_OF_TOTAL
    1
    		2025-04-28 00:00:00.000Asgardex1676263.693754452688722050.46461676263.69375445114778976.3625224776629.62478186599832901.57722535.09302218219.135158485
    2
    		2025-04-28 00:00:00.000Bitget342680.302599739278897634.160343342680.30259973950023938.51995244776629.62478186599832901.5772257.1741024438.339645656
    3
    		2025-04-28 00:00:00.000ttx12.31225107912.31225107912.31225107912.3122510794776629.62478186599832901.5772250.00025776022110.000002052613496
    4
    		2025-04-28 00:00:00.000ShapeShift777.0358546261242403143.36341777.0358546268220279.883066234776629.62478186599832901.5772250.016267450391.370428308
    5
    		2025-04-28 00:00:00.000THORSwap534404.52582556390201843.64177534404.525825575816089.40650014776629.62478186599832901.57722511.18789958212.639534978
    6
    		2025-04-28 00:00:00.000THORWallet1881271.370223471122553944.810161881271.37022347130834247.2162734776629.62478186599832901.57722539.38491191521.811782393
    7
    		2025-04-28 00:00:00.000rj3.76838831320893.4865477633.76838831314589.1408366624776629.62478186599832901.5772250.000078892202410.002432200834
    8
    		2025-04-28 00:00:00.000Others110897.882711313993202496.369641110897.88271131359564611.1967764776629.62478186599832901.5772252.32167649.930200734
    9
    		2025-04-28 00:00:00.000Ledger351.132764501503541439.529509351.13276450146457021.44231284776629.62478186599832901.5772250.007351056967.744993867
    10
    		2025-04-28 00:00:00.000Trustwallet229967.6004088715166886824.16758229967.600408871113540948.5578934776629.62478186599832901.5772254.81443231918.928763037
    11
    		2025-04-27 00:00:00.000Trustwallet2124698.280343495166656856.5671731418697.6328452113310980.957484135867428.558641599832901.57722523.1245251118.890424426
    12
    		2025-04-27 00:00:00.000THORWallet4850975.352060281120672673.4399432855867.2515061128952975.846049135867428.558641599832901.57722524.18229858321.498149819
    13
    		2025-04-27 00:00:00.000eld18979.896118487102706.0926918734162.451599652100404.477204785135867428.558641599832901.5772250.025143959790.01673874123
    14
    		2025-04-27 00:00:00.000Bitget470990.941137463278554953.85774312481063.807066249681258.2173527135867428.558641599832901.5772259.1862074238.282516362
    15
    		2025-04-27 00:00:00.000Ledger127604.74971013503541088.3967446294820.7351982546456670.3095483135867428.558641599832901.5772254.633060927.744935329
    16
    		2025-04-27 00:00:00.000Asgardex2273408.436158912687045786.7708423528871.288573113102712.668768135867428.558641599832901.57722517.317521618.855703375
    17
    		2025-04-27 00:00:00.000Others203966.453341411993091598.486935151289.2304567759453713.3140646135867428.558641599832901.5772253.7914084979.911712605
    18
    		2025-04-27 00:00:00.000-2.8839701172448.615794464.311450375494.8954744135867428.558641599832901.5772250.0000031732773780.00008250555665
    19
    		2025-04-27 00:00:00.000rj513.71887779420889.718159453742.75194686314585.372448349135867428.558641599832901.5772250.0027547087530.002431572595
    20
    		2025-04-27 00:00:00.000THORSwap1049175.374900146389667439.1159423327484.888604575281684.8806746135867428.558641599832901.57722517.16929887912.550442745
    ...
    11009
    2MB
    22s