-- forked from https://flipsidecrypto.xyz/BlockTracker/q/FtItxETrSwWq/thruster-vs-trader-joe --Thruster Credit Goes To: BlockTracker || https://flipsidecrypto.xyz/BlockTracker/dashboards with Thruster_V2 as ( with pool_created as ( select contract_address, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0, lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1, lower('0x' || substr(segmented_data[0]::string, 25, 40 ) :: string) as pool_address from blast.core.fact_event_logs WHERE topics[0] = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9' and tx_status = 'SUCCESS' and contract_address IN ('0xb4a7d971d0adea1c73198c97d7ab3f9ce4aafa13', '0x37836821a2c03c171fb1a595767f4a16e2b93fc4') -- ThrusterFactory V2 ) , swap as ( select block_timestamp, block_number, origin_from_address, origin_to_address, ORIGIN_FUNCTION_SIGNATURE, tx_hash, event_index, contract_address, lower('0x' || substr(topics[1]::string, 27, 40) :: string) as sender, -- thruster - v2 (0.3% - 1%) lower('0x' || substr(topics[2]::string, 27, 40) :: string) as address_to, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, livequery.utils.udf_hex_to_int(segmented_data[0]::string) :: int as amount0In, livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int as amount1In, livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int as amount0Out, livequery.utils.udf_hex_to_int(segmented_data[3]::string) :: int as amount1Out from blast.core.fact_event_logs where topics[0]::string = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822' and block_timestamp::date >= '2024-02-28' and tx_status = 'SUCCESS'), result as ( select a.* , token0, token1 from swap a join pool_created b on a.contract_address = b.pool_address) SELECT block_number, block_timestamp, tx_hash, origin_from_address, origin_to_address, origin_function_signature, event_index, contract_address, sender, address_to, CASE-- fork from dbt Docs flipside WHEN amount0In <> 0 AND amount1In <> 0 AND amount0Out <> 0 THEN amount1In WHEN amount0In <> 0 THEN amount0In WHEN amount1In <> 0 THEN amount1In END AS amount_in_raw, CASE WHEN amount0Out <> 0 THEN amount0Out WHEN amount1Out <> 0 THEN amount1Out END AS amount_out_raw, CASE WHEN amount0In <> 0 AND amount1In <> 0 AND amount0Out <> 0 THEN token1 WHEN amount0In <> 0 THEN token0 WHEN amount1In <> 0 THEN token1 END AS token_in, CASE WHEN amount0Out <> 0 THEN token0 WHEN amount1Out <> 0 THEN token1 END AS token_out, 'Swap' AS event_name, 'Thruster-v2' AS platform from result ), Thruster_V3 as ( with created_pools as ( select REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0, lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1, lower('0x' || substr(segmented_data[1]::string, 25, 40 ) :: string)as pool_address from blast.core.fact_event_logs where contract_address = '0x71b08f13b3c3af35aadeb3949afeb1ded1016127' and topics[0] = '0x783cca1c0412dd0d695e784568c96da2e9c22ff989357a2e8b1d9b2b4e6b7118' and tx_status = 'SUCCESS'), swap as ( select block_timestamp, block_number, origin_from_address, origin_to_address, ORIGIN_FUNCTION_SIGNATURE, tx_hash, event_index, contract_address, lower('0x' || substr(topics[1]::string, 27, 40) :: string) as sender_address, lower('0x' || substr(topics[2]::string, 27, 40) :: string) as recipient_address, REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data, livequery.utils.udf_hex_to_int('s2c',segmented_data[0]::string) :: int as amount_0, livequery.utils.udf_hex_to_int('s2c',segmented_data[1]::string) :: int as amount_1 from blast.core.fact_event_logs where topics[0] = '0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67' and block_timestamp::date >= '2024-02-28' and tx_status = 'SUCCESS' ), result as ( select block_number, block_timestamp, tx_hash, origin_from_address, origin_to_address, origin_function_signature, event_index, contract_address, sender_address as sender, recipient_address as address_to, amount_0 as amount_0_raw, amount_1 as amount_1_raw, token0, token1 from swap a join created_pools b ON a.contract_address = b.pool_address) select block_number, block_timestamp, tx_hash, origin_from_address, origin_to_address, origin_function_signature, event_index, contract_address, sender, address_to, IFF(amount_0_raw > 0, ABS(amount_0_raw), ABS(amount_1_raw)) as amount_in_raw, IFF(amount_0_raw < 0, ABS(amount_0_raw), ABS(amount_1_raw)) as amount_out_raw, IFF(amount_0_raw > 0, token0, token1) as token_in, IFF(amount_0_raw < 0, token0, token1) as token_out , 'Swap' AS event_name, 'Thruster-v3' AS platform from result), main_m as ( select * from Thruster_V2 union all select * from Thruster_V3), token_info as ( select distinct token_address, symbol, decimals from blast.price.ez_prices_hourly), prices as ( select date_trunc('day', hour) as date, token_address, avg (price) as usd_price from blast.price.ez_prices_hourly where hour::date >= '2024-02-28' group by 1,2), final as ( select m.*, amount_in_raw / pow(10, c1.decimals) as amount_in, amount_out_raw / pow(10, c2.decimals) as amount_out, c1.symbol as symbol_in, c2.symbol as symbol_out from main_m m left join token_info c1 ON m.token_in = c1.token_address left join token_info c2 ON m.token_out = c2.token_address), add_usd_price as ( select f.* , amount_in * p1.usd_price as amount_in_usd, amount_out * p2.usd_price as amount_out_usd from final f left outer join prices p1 ON f.token_in = p1.token_address and date_trunc('day', block_timestamp) = p1.date left outer join prices p2 ON f.token_out = p2.token_address and date_trunc('day', block_timestamp) = p2.date) select 'Thruster' as platform, block_timestamp::date as date, count(distinct tx_hash) as Swaps, count(distinct origin_from_address) as Traders, sum (coalesce(amount_in_usd,amount_out_usd)) as Volume, avg (coalesce(amount_in_usd,amount_out_usd)) as Average_Volume, sum (Swaps) over (order by date) as Cumulative_Swaps, sum (Volume) over (order by date) as Cumulative_Volume from add_usd_price where block_timestamp >= '{{Start_Date}}' and block_timestamp <= '{{End_Date}}' group by 1,2 union all select 'Trader Joe' as platform, block_timestamp::date as date, count (distinct tx_hash) as Swaps, count (distinct origin_from_address) as Traders, sum (coalesce(amount_in_usd,amount_out_usd)) as Volume, avg (coalesce(amount_in_usd,amount_out_usd)) as Average_Volume, sum (Swaps) over (order by date) as Cumulative_Swaps, sum (Volume) over (order by date) as Cumulative_Volume from avalanche.defi.ez_dex_swaps where platform ilike '%joe%' and block_timestamp >= '{{Start_Date}}' and block_timestamp <= '{{End_Date}}' group by 1,2 order by date desc,platform asc

Avalanche's TraderJoe vs Blast's Thruster