-- forked from h4wk / all 1 @ https://flipsidecrypto.xyz/h4wk/q/urVEBMEZpzRz/all-1 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 ) select * from final_Ethereum order by date desc --------------------------------------------------- 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
all 1 copy bsc arbitrum base ethereum prices	nazihkalo	May 6, 2024
2023-08-14 12:44 PM base ethereum l2 evm balances	nazihkalo	Aug 14, 2023

nazihkalo