-- forked from h4wk / all 1 bridge to @ https://flipsidecrypto.xyz/h4wk/q/gHB9K7UX8S7k/all-1-bridge-to -- forked from all 1 @ https://flipsidecrypto.xyz/edit/queries/01000516-e51f-4d15-a501-43ca7ef4ab99 with ethereum_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Ethereum.price.ez_hourly_token_prices group by 1,2,3,4 ) , ETH_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as ETH_price from ethereum.price.ez_hourly_token_prices where symbol = 'WETH' group by 1,2,3,4 ) , base_from_Ethereum as ( select distinct tx_hash from Ethereum.core.fact_token_transfers t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , ethereum_cancelled_tx as ( select tx_hash FROM ethereum.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Ethereum as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Ethereum.core.fact_token_transfers t join ethereum_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_Ethereum) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Ethereum_ETH as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, p.symbol, amount - 0.001 as real_amount, real_amount*eth_price as volume_usd, origin_from_address as user_address, tx_hash from Ethereum.core.EZ_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Ethereum as ( select block_timestamp::date as date, 'To' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Ethereum.core.fact_token_transfers t join ethereum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Ethereum_ETH as ( select block_timestamp::date as date, 'To' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Ethereum.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Ethereum_others as ( select block_timestamp::date as date, 'To' as route_type, 'Ethereum' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Ethereum.core.fact_token_transfers t join ethereum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Ethereum) and tx_hash not in (select tx_hash from to_Ethereum_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_ethereum as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from ethereum.core.fact_token_transfers join ethereum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from ethereum_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_ethereum_eth as ( select block_timestamp::date as date, 'From' as route_type, 'Ethereum' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*eth_price as volume_usd, TO_ADDRESS as user_address, tx_hash from ethereum.core.EZ_native_TRANSFERS join eth_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from ethereum_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , base_Ethereum as ( select * from to_Ethereum UNION select * from to_Ethereum_ETH UNION select * from to_Ethereum_others -- UNION -- select * from from_Ethereum -- UNION -- select * from from_Ethereum_ETH -- UNION -- select * from cancel_ethereum -- UNION -- select * from cancel_ethereum_eth ) , final_Ethereum as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Ethereum join ETH_price on (date = price_date) group by 1,2 ) --------------------------------------------------- Polygon , polygon_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from polygon.price.ez_hourly_token_prices group by 1,2,3,4 ) , MATIC_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as MATIC_price from polygon.price.ez_hourly_token_prices where symbol = 'MATIC' group by 1,2,3,4 ) , base_from_polygon as ( select distinct tx_hash from polygon.core.fact_token_transfers t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , polygon_cancelled_tx as ( select tx_hash FROM polygon.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_polygon as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from polygon.core.fact_token_transfers t join polygon_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_polygon) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_polygon_MATIC as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Creation' as tx_type, p.symbol, amount - 0.5 as real_amount, real_amount*MATIC_price as volume_usd, origin_from_address as user_address, tx_hash from polygon.core.EZ_native_TRANSFERS t join MATIC_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_polygon as ( select block_timestamp::date as date, 'To' as route_type, 'polygon' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from polygon.core.fact_token_transfers t join polygon_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_polygon_MATIC as ( select block_timestamp::date as date, 'To' as route_type, 'polygon' as chain, 'Creation' as tx_type, 'MATIC' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from polygon.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_polygon_others as ( select block_timestamp::date as date, 'To' as route_type, 'polygon' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from polygon.core.fact_token_transfers t join polygon_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_polygon) and tx_hash not in (select tx_hash from to_polygon_MATIC) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_polygon as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from polygon.core.fact_token_transfers join polygon_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from polygon_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_polygon_MATIC as ( select block_timestamp::date as date, 'From' as route_type, 'polygon' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*MATIC_price as volume_usd, TO_ADDRESS as user_address, tx_hash from polygon.core.EZ_native_TRANSFERS join MATIC_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from polygon_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.5 ) , base_polygon as ( select * from to_polygon UNION select * from to_polygon_MATIC UNION select * from to_polygon_others -- UNION -- select * from from_polygon -- UNION -- select * from from_polygon_MATIC -- UNION -- select * from cancel_polygon -- UNION -- select * from cancel_polygon_MATIC ) , final_polygon as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.5 * MATIC_price when (route_type = 'From' and tx_type = 'Cancel') then -0.5 * MATIC_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_polygon join MATIC_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) ------------------------------------------------ Solana , base_sol_tx as ( select distinct tx_id, index from solana.core.fact_events where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and program_id = 'src5qyZHqTqecJV4aY6Cb6zDZLMDzrDKKezs22MPHr4' ) , base_sol_to as ( select distinct tx_id, index from solana.core.fact_events where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and program_id = 'dst5MGcFPoBeREFAA5E3tU5ij8m5uVYwkzkSAbsLbNo' and signers[0] = '7FfB2zQRYUQwpPzkRxAeg2mCBGeCRKp4PCEeULJA9xTo' ) , solana_price as ( select recorded_hour::date as price_date, upper(symbol) as symbol, token_address, avg(close) as price from solana.price.ez_token_prices_hourly group by 1,2,3 ) , sol_price as ( select recorded_hour::date as price_date, upper(symbol) as symbol, token_address, avg(close) as sol_price from solana.price.ez_token_prices_hourly where upper(symbol) = 'SOL' group by 1,2,3 ) , from_solana as ( select block_timestamp::date as date, 'From' as type, 'Solana' as chain, upper(symbol) as symbol, amount, amount*price as volume_usd, tx_from as user_address, t.tx_id from solana.core.fact_transfers t join base_sol_tx b on (t.tx_id = b.tx_id) join solana_price sp on (mint = token_address and date = price_date) where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and amount != 0.01735944 qualify row_number() over (partition by t.tx_id order by t.index) = 1 ) , to_solana as ( select block_timestamp::date as date, 'To' as type, 'Solana' as chain, upper(symbol) as symbol, amount, amount*price as volume_usd, tx_to as user_address, t.tx_id from solana.core.fact_transfers t join base_sol_to b on (t.tx_id = b.tx_id) join solana_price sp on (mint = token_address and date = price_date) where block_timestamp >= '2023-07-01' and block_timestamp < CURRENT_DATE and tx_from = '7FfB2zQRYUQwpPzkRxAeg2mCBGeCRKp4PCEeULJA9xTo' qualify row_number() over (partition by t.tx_id order by t.index desc) = 1 ) , base_solana as ( -- select * from from_solana -- UNION select * from to_solana ) , final_solana as ( select date, -- type, chain, -- symbol, zeroifnull(sum(case when type = 'From' then 0.015 * sol_price end)) as fee_usd, sum(volume_usd) as amount_usd, amount_usd *0.04 * 0.01 as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, 0 as cancel_txs, count(distinct tx_id) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_solana join sol_price on (date = price_date) group by 1,2 ) -------------------------------------------------- BSC , bsc_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from BSC.price.ez_hourly_token_prices group by 1,2,3,4 ) , BNB_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as BNB_price from BSC.price.ez_hourly_token_prices where symbol = 'WBNB' group by 1,2,3,4 ) , base_from_BSC as ( select distinct tx_hash from BSC.core.fact_token_transfers t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , bsc_cancelled_tx as ( select tx_hash FROM BSC.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_BSC as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from BSC.core.fact_token_transfers t join bsc_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_BSC) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_BSC_BNB as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Creation' as tx_type, p.symbol, amount - 0.005 as real_amount, real_amount*BNB_price as volume_usd, origin_from_address as user_address, tx_hash from BSC.core.EZ_native_TRANSFERS t join BNB_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_BSC as ( select block_timestamp::date as date, 'To' as route_type, 'BSC' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from BSC.core.fact_token_transfers t join bsc_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_BSC_BNB as ( select block_timestamp::date as date, 'To' as route_type, 'BSC' as chain, 'Creation' as tx_type, 'BNB' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from BSC.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_BSC_others as ( select block_timestamp::date as date, 'To' as route_type, 'BSC' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from BSC.core.fact_token_transfers t join bsc_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_BSC) and tx_hash not in (select tx_hash from to_BSC_BNB) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_BSC as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from BSC.core.fact_token_transfers join bsc_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from bsc_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_BSC_BNB as ( select block_timestamp::date as date, 'From' as route_type, 'BSC' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*BNB_price as volume_usd, TO_ADDRESS as user_address, tx_hash from BSC.core.EZ_native_TRANSFERS join BNB_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from bsc_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.005 ) , base_BSC as ( select * from to_BSC UNION select * from to_BSC_BNB UNION select * from to_BSC_others -- UNION -- select * from from_BSC -- UNION -- select * from from_BSC_BNB -- UNION -- select * from cancel_BSC -- UNION -- select * from cancel_BSC_BNB ) , final_BSC as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.005 * BNB_price when (route_type = 'From' and tx_type = 'Cancel') then -0.005 * BNB_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_BSC join BNB_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) ---------------------------------------- Avalanche , avalanche_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Avalanche.price.ez_hourly_token_prices group by 1,2,3,4 ) , AVAX_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as AVAX_price from Avalanche.price.ez_hourly_token_prices where symbol = 'WAVAX' group by 1,2,3,4 ) , base_from_Avalanche as ( select distinct tx_hash from Avalanche.core.fact_token_transfers t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , avalanche_cancelled_tx as ( select tx_hash FROM Avalanche.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Avalanche as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Avalanche.core.fact_token_transfers t join avalanche_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_Avalanche) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Avalanche_AVAX as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, p.symbol, amount - (case when date >= '2023-10-16' then 0.05 else 0.01 end) as real_amount, real_amount*AVAX_price as volume_usd, origin_from_address as user_address, tx_hash from Avalanche.core.EZ_native_TRANSFERS t join AVAX_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Avalanche as ( select block_timestamp::date as date, 'To' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Avalanche.core.fact_token_transfers t join avalanche_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Avalanche_AVAX as ( select block_timestamp::date as date, 'To' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, 'AVAX' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Avalanche.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Avalanche_others as ( select block_timestamp::date as date, 'To' as route_type, 'Avalanche' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Avalanche.core.fact_token_transfers t join avalanche_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Avalanche) and tx_hash not in (select tx_hash from to_Avalanche_AVAX) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_Avalanche as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Avalanche.core.fact_token_transfers join avalanche_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from avalanche_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Avalanche_AVAX as ( select block_timestamp::date as date, 'From' as route_type, 'Avalanche' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*AVAX_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Avalanche.core.EZ_native_TRANSFERS join AVAX_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from avalanche_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != (case when date >= '2023-10-16' then 0.05 else 0.01 end) ) , base_Avalanche as ( select * from to_Avalanche UNION select * from to_Avalanche_AVAX UNION select * from to_Avalanche_others -- UNION -- select * from from_Avalanche -- UNION -- select * from from_Avalanche_AVAX -- UNION -- select * from cancel_Avalanche -- UNION -- select * from cancel_Avalanche_AVAX ) , final_Avalanche as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then (case when date >= '2023-10-16' then 0.05 else 0.01 end) * AVAX_price when (route_type = 'From' and tx_type = 'Cancel') then (case when date >= '2023-10-16' then -0.05 else -0.01 end) * AVAX_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Avalanche join AVAX_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) -------------------------------------------- Optimism , Optimism_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Optimism.price.ez_hourly_token_prices group by 1,2,3,4 ) , Optimism_cancelled_tx as ( select tx_hash FROM Optimism.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , base_from_optimism as ( select distinct tx_hash from Optimism.core.fact_token_transfers t where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , from_Optimism as ( select block_timestamp::date as date, 'From' as route_type, 'Optimism' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Optimism.core.fact_token_transfers t join Optimism_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_optimism) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Optimism_ETH as ( select block_timestamp::date as date, 'From' as route_type, 'Optimism' as chain, 'Creation' as tx_type, p.symbol, amount - 0.001 as real_amount, real_amount*eth_price as volume_usd, origin_from_address as user_address, tx_hash from Optimism.core.EZ_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Optimism as ( select block_timestamp::date as date, 'To' as route_type, 'Optimism' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Optimism.core.fact_token_transfers t join Optimism_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Optimism_ETH as ( select block_timestamp::date as date, 'To' as route_type, 'Optimism' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Optimism.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Optimism_others as ( select block_timestamp::date as date, 'To' as route_type, 'Optimism' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Optimism.core.fact_token_transfers t join Optimism_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= '2023-08-01' and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Optimism) and tx_hash not in (select tx_hash from to_Optimism_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_Optimism as ( select block_timestamp::date as date, 'From' as route_type, 'Optimism' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Optimism.core.fact_token_transfers join Optimism_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from Optimism_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Optimism_ETH as ( select block_timestamp::date as date, 'From' as route_type, 'Optimism' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*ETH_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Optimism.core.EZ_native_TRANSFERS join ETH_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from Optimism_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , base_Optimism as ( select * from to_Optimism UNION select * from to_Optimism_ETH UNION select * from to_Optimism_others -- UNION -- select * from from_Optimism -- UNION -- select * from from_Optimism_ETH -- UNION -- select * from cancel_Optimism -- UNION -- select * from cancel_Optimism_ETH ) , final_Optimism as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Optimism join ETH_price on (date = price_date) -- where route_type = 'From' group by 1,2 ) ------------------------------------------------------- Arbitrum , arbitrum_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Arbitrum.price.ez_hourly_token_prices group by 1,2,3,4 ) , base_from_Arbitrum as ( select distinct tx_hash from Arbitrum.core.fact_token_transfers t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , arbitrum_cancelled_tx as ( select tx_hash FROM Arbitrum.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Arbitrum as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Arbitrum.core.fact_token_transfers t join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_Arbitrum) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Arbitrum_ETH as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, p.symbol, amount - 0.001 as real_amount, real_amount*eth_price as volume_usd, origin_from_address as user_address, tx_hash from Arbitrum.core.EZ_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Arbitrum as ( select block_timestamp::date as date, 'To' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Arbitrum.core.fact_token_transfers t join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Arbitrum_ETH as ( select block_timestamp::date as date, 'To' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Arbitrum.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Arbitrum_others as ( select block_timestamp::date as date, 'To' as route_type, 'Arbitrum' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Arbitrum.core.fact_token_transfers t join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Arbitrum) and tx_hash not in (select tx_hash from to_Arbitrum_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_Arbitrum as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Arbitrum.core.fact_token_transfers join arbitrum_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from arbitrum_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Arbitrum_eth as ( select block_timestamp::date as date, 'From' as route_type, 'Arbitrum' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*eth_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Arbitrum.core.EZ_native_TRANSFERS join eth_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from arbitrum_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , base_Arbitrum as ( select * from to_Arbitrum UNION select * from to_Arbitrum_ETH UNION select * from to_Arbitrum_others -- UNION -- select * from from_Arbitrum -- UNION -- select * from from_Arbitrum_ETH -- UNION -- select * from cancel_Arbitrum -- UNION -- select * from cancel_Arbitrum_eth ) , final_Arbitrum as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Arbitrum join ETH_price on (date = price_date) group by 1,2 ) ---------------------------------------------------------------- Base , base_price as ( select hour::date as price_date, token_address, symbol, decimals, avg(price) as price from Base.price.ez_hourly_token_prices group by 1,2,3,4 ) , base_from_Base as ( select distinct tx_hash from Base.core.fact_token_transfers t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and to_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , base_cancelled_tx as ( select tx_hash FROM Base.core.fact_event_logs where 1 = 1 and block_timestamp > CURRENT_DATE - 90 and topics[0] = '0x7d7d1c5b3eadbe275ceb358e65cd57410b35997187258dbaaae42ab6e1405fd8' ) , from_Base as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Creation' as tx_type, p.symbol, raw_amount/pow(10,p.decimals) as amount, amount*price as volume_usd, origin_from_address as user_address, tx_hash from Base.core.fact_token_transfers t join base_price p ON (contract_address = token_address and block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and tx_hash in (select tx_hash from base_from_Base) qualify row_number() over (partition by tx_hash order by event_index) = 1 ) , from_Base_ETH as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Creation' as tx_type, p.symbol, amount - 0.001 as real_amount, real_amount*eth_price as volume_usd, origin_from_address as user_address, tx_hash from Base.core.EZ_native_TRANSFERS t join eth_price p ON (block_timestamp::date = p.price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and TO_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and real_amount != 0 ) , to_Base as ( select block_timestamp::date as date, 'To' as route_type, 'Base' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Base.core.fact_token_transfers t join base_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and ( from_address in ('0x555ce236c0220695b68341bc48c68d52210cc35b', '0x55f6265a266f7202835b5661c1da045f39f2e587') or origin_to_address = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' ) and to_address != '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' ) , to_Base_ETH as ( select block_timestamp::date as date, 'To' as route_type, 'Base' as chain, 'Creation' as tx_type, 'ETH' as symbol, amount, amount_usd as volume_usd, TO_ADDRESS as user_address, tx_hash from Base.core.EZ_native_TRANSFERS t where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE -- tx_hash = '0x0595daadd276a7fa014ca8ac0c83ef07999fecc34559bb35c25bd79a9b779b8d' and from_ADDRESS = '0xe7351fd770a37282b91d153ee690b63579d6dd7f' and TO_ADDRESS != '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' ) , to_Base_others as ( select block_timestamp::date as date, 'To' as route_type, 'Base' as chain, 'Creation' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Base.core.fact_token_transfers t join base_price p ON (contract_address = token_address and block_timestamp::date = price_date) where 1 = 1 and block_timestamp >= CURRENT_DATE - 90 and block_timestamp < CURRENT_DATE and origin_to_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and from_address = '0xc31fc94f3fd088ee53ac915d6e8a14ff25a23c47' and tx_hash not in (select tx_hash from to_Base) and tx_hash not in (select tx_hash from to_Base_ETH) qualify row_number() over (partition by tx_hash order by event_index desc) = 1 ) , cancel_Base as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Cancel' as tx_type, symbol, raw_amount/pow(10,decimals) as amount, amount*price as volume_usd, to_address as user_address, tx_hash from Base.core.fact_token_transfers join base_price p ON (contract_address = token_address and block_timestamp::date = price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from base_cancelled_tx) and from_address = '0xef4fb24ad0916217251f553c0596f8edc630eb66' ) , cancel_Base_eth as ( select block_timestamp::date as date, 'From' as route_type, 'Base' as chain, 'Cancel' as tx_type, p.symbol, amount, amount*eth_price as volume_usd, TO_ADDRESS as user_address, tx_hash from Base.core.EZ_native_TRANSFERS join eth_price p ON (block_timestamp::date = p.price_date) where block_timestamp > CURRENT_DATE - 90 and tx_hash in (select tx_hash from base_cancelled_tx) and from_ADDRESS = '0xef4fb24ad0916217251f553c0596f8edc630eb66' and amount != 0.001 ) , base_Base as ( select * from to_Base UNION select * from to_Base_ETH UNION select * from to_Base_others -- UNION -- select * from from_Base -- UNION -- select * from from_Base_ETH -- UNION -- select * from cancel_Base -- UNION -- select * from cancel_Base_eth ) , final_Base as ( select date, chain, zeroifnull(sum(case when (route_type = 'From' and tx_type != 'Cancel') then 0.001 * ETH_price when (route_type = 'From' and tx_type = 'Cancel') then -0.001 * ETH_price end)) as fee_usd, sum(case when tx_type != 'Cancel' then volume_usd else -volume_usd end) as amount_usd, case when fee_usd != 0 then amount_usd *0.04 * 0.01 else 0 end as variable_fee, fee_usd + variable_fee as total_fee, count(distinct user_address) as user_count, count(distinct case when tx_type = 'Cancel' then tx_hash end) as cancel_txs, count(distinct tx_hash) as all_txs, all_txs - cancel_txs*2 as tx_count, -- deducted creation txs as well sum(amount_usd) over (partition by chain order by date) as cumu_usd, sum(tx_count) over (partition by chain order by date) as cumu_tx, sum(total_fee) over (partition by chain order by date) as cumu_fee from base_Base join ETH_price on (date = price_date) group by 1,2 ) , final_all as ( select * from final_ethereum UNION select * from final_polygon UNION select * from final_solana UNION select * from final_bsc UNION select * from final_avalanche UNION select * from final_optimism UNION select * from final_arbitrum UNION select * from final_Base ) select * from final_all where date < CURRENT_DATE

Query	Creator	Created
Jumper.Exchange (EVM --> Solana) copy gnosis bsc arbitrum base ethereum	sleeveofwizard	May 27, 2024
stableUSDC copy arbitrum base ethereum optimism avalanche	sleeveofwizard	Apr 19, 2024
all 1 bridge to copy bsc arbitrum base ethereum prices	sleeveofwizard	May 11, 2024
all 1 bridge to copy bsc arbitrum base ethereum prices	sleeveofwizard	May 11, 2024

sleeveofwizard