with uniswap_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 boba.core.fact_event_logs WHERE topics[0] = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9' and TX_SUCCEEDED ) , 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, 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 boba.core.fact_event_logs where topics[0]::string = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822' and TX_SUCCEEDED ) , 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 -- Credit to flipside team 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, 'Uniswap-v2' AS platform from result ) , uniswap_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 boba.core.fact_event_logs where topics[0] = '0x783cca1c0412dd0d695e784568c96da2e9c22ff989357a2e8b1d9b2b4e6b7118' and TX_SUCCEEDED ) , 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 ink.core.fact_event_logs where topics[0] = '0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67' and TX_SUCCEEDED ) , 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, 'Uniswap-v3' AS platform from result ) , main_m as ( select * from Uniswap_V2 union all select * from Uniswap_V3 ) , token_info as ( select address as token, symbol, name, decimals from boba.core.dim_contracts group by all ) , prices as ( select date_trunc('hour', hour) as date, token_address, avg(price) as usd_price from boba.price.ez_prices_hourly 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 left join token_info c2 ON m.token_out = c2.token ), 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('hour', block_timestamp) = p1.date left outer join prices p2 ON f.token_out = p2.token_address and date_trunc('hour', block_timestamp) = p2.date ) select date_trunc('month', block_timestamp) as date, 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(coalesce(amount_in_usd,amount_out_usd)) 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(coalesce(amount_in_usd,amount_out_usd)) as avg_volume_usd from add_usd_price where date >= '2021-01-01' and sender = '0x17c83e2b96acfb5190d63f5e46d93c107ec0b514' -- Oolong Swap router group by 1 order by date desc -- select * -- from add_usd_price -- where tx_hash = '0xeb63ab3f335353797358efd645e0d145b9e57b97ca6b091e41e7ac81bc079169' -- -- tx_hahs = '0xeb63ab3f335353797358efd645e0d145b9e57b97ca6b091e41e7ac81bc079169'