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 platform, sum(case when date > current_date - 7 then volume_usd end) as volume_7_days, sum(case when date > current_date - 30 then volume_usd end) as volume_30_days from ( select date_trunc('day', block_timestamp) as date, 'Thruster' as platform, count(DISTINCT tx_hash) as n_swaps, sum(n_swaps) over (order by date) as cum_swaps, count(DISTINCT origin_from_address) as n_swappers, sum(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as volume_usd, sum(volume_usd) over (order by date) as cum_volume, avg(volume_usd) over (order by date) as d_avg_volume, avg(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as avg_volume_usd from add_usd_price where block_timestamp::date >= current_date - 60 and block_timestamp::date < current_date group by date -- trader joe (Avalanche) -- union all -- select -- date_trunc('day', block_timestamp) as date, -- 'Trader Joe' as platform, -- count(DISTINCT tx_hash) as n_swaps, -- sum(n_swaps) over (order by date) as cum_swaps, -- count(DISTINCT origin_from_address) as n_swappers, -- sum(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as volume_usd, -- sum(volume_usd) over (order by date) as cum_volume, -- avg(volume_usd) over (order by date) as d_avg_volume, -- avg(case when amount_in_usd is not null then amount_in_usd else amount_out_usd end) as avg_volume_usd -- from avalanche.defi.ez_dex_swaps -- where block_timestamp::date >= current_date - 60 -- and platform ilike '%trader-joe%' -- group by 1 union all select date_trunc('day', block_timestamp) as date, 'Trader Joe' as platform, count(DISTINCT tx_hash) as n_swaps, sum(n_swaps) over (order by date) as cum_swaps, count(DISTINCT trader) as n_swappers, sum(case when amount_in_usd < amount_out_usd then amount_in_usd else amount_out_usd end) as volume_usd, sum(volume_usd) over (order by date) as cum_volume, avg(volume_usd) over (order by date) as d_avg_volume, avg(case when amount_in_usd < amount_out_usd then amount_in_usd else amount_out_usd end) as avg_volume_usd from crosschain.defi.ez_dex_swaps where block_timestamp::date >= current_date - 60 and block_timestamp::date < current_date and platform ilike '%trader-joe%' group by 1 ) group by 1 order by 1