with prices as ( select date_trunc('day', hour) as day, token_address, avg( price ) as price from arbitrum.core.fact_hourly_token_prices -- where hour >= current_date - interval '1 day' group by 1,2 ), arbitrum as ( with inflow as ( select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct origin_from_address ) as users, sum( amount_usd ) as volume from arbitrum.core.ez_token_transfers a join arbitrum.core.fact_transactions b on a.tx_hash = b.tx_hash where origin_to_address in ( '0x53bf833a5d6c4dda888f69c22c88c9f356a41614', -- stable router '0xbf22f0f184bccbea268df387a49ff5238dd23e40' -- eth router ) and b.to_address in ('0x53bf833a5d6c4dda888f69c22c88c9f356a41614', '0xbf22f0f184bccbea268df387a49ff5238dd23e40' ) -- layerzero and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ), outflow as ( select date_trunc('{{Date_Trunc}}', block_timestamp) as date, -- symbol, count( distinct tx_hash ) as transactions, count( distinct origin_to_address ) as users, sum( amount ) as volume from( select a.contract_address, a.block_timestamp, b.tx_hash, origin_to_address, (a.amount) as amount from arbitrum.core.ez_token_transfers a join arbitrum.core.fact_transactions b on a.tx_hash = b.tx_hash left join prices c on a.contract_address = c.token_address where a.from_address in ( '0x53bf833a5d6c4dda888f69c22c88c9f356a41614', '0xbf22f0f184bccbea268df387a49ff5238dd23e40' ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' ) group by 1 ) select substring(a.date, 1, 10) as date, 'Arbitrum' as chain, -- a.transactions as in_transactions, -- b.transactions as out_transactions, -- in_transactions - out_transactions as net_transactions, a.users as in_users, b.users as out_users, in_users - out_users as net_users, a.volume as inflow, b.volume as outflow, inflow - outflow as netflow from inflow a left join outflow b on a.date = b.date ), ethereum as ( with inflow as ( select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct origin_from_address ) as users, sum( amount_usd ) as volume from ethereum.core.ez_token_transfers a left join ethereum.core.fact_transactions b on a.tx_hash = b.tx_hash where origin_to_address in ( -- stable router lower('0x66A71Dcef29A0fFBDBE3c6a460a3B5BC225Cd675'), -- eth router lower('0x8731d54E9D02c286767d56ac03e8037C07e01e98') ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 union select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct origin_from_address ) as users, sum( amount_usd ) as volume from ethereum.core.ez_token_transfers a left join ethereum.core.fact_transactions b on a.tx_hash = b.tx_hash where origin_to_address in ( -- stable router lower('0x66A71Dcef29A0fFBDBE3c6a460a3B5BC225Cd675'), -- eth router lower('0x8731d54E9D02c286767d56ac03e8037C07e01e98') ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ), outflow as ( select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct origin_to_address ) as users, sum( amount_usd ) as volume from ethereum.core.ez_token_transfers a left join ethereum.core.fact_transactions b on a.tx_hash = b.tx_hash where origin_from_address in ( -- stable router lower('0x66A71Dcef29A0fFBDBE3c6a460a3B5BC225Cd675'), -- eth router lower('0x8731d54E9D02c286767d56ac03e8037C07e01e98') -- eth router ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 union select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct origin_to_address ) as users, sum( amount_usd ) as volume from ethereum.core.ez_eth_transfers a left join ethereum.core.fact_transactions b on a.tx_hash = b.tx_hash where eth_from_address in ( -- stable router lower('0x66A71Dcef29A0fFBDBE3c6a460a3B5BC225Cd675'), -- eth router lower('0x8731d54E9D02c286767d56ac03e8037C07e01e98') -- eth router ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ) select substring(a.date, 1, 10) as date, 'ethereum' as chain, -- a.transactions as in_transactions, -- b.transactions as out_transactions, -- in_transactions - out_transactions as net_transactions, a.users as in_users, b.users as out_users, in_users - out_users as net_users, a.volume as inflow, b.volume as outflow, inflow - outflow as netflow from inflow a left join outflow b on a.date = b.date ), polygon as ( with inflow as ( select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct a.from_address ) as users, sum( amount_usd ) as volume from polygon.core.ez_token_transfers a left join polygon.core.fact_transactions b on a.tx_hash = b.tx_hash where origin_to_address in ( lower('0x45A01E4e04F14f7A4a6702c74187c5F6222033cd') ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ), outflow as ( -- nothing returned from token transfers so just working with matic select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct a.origin_to_address ) as users, sum( amount_usd ) as volume from polygon.core.ez_matic_transfers a left join polygon.core.fact_transactions b on a.tx_hash = b.tx_hash where matic_from_address in ( -- stable router lower('0x45A01E4e04F14f7A4a6702c74187c5F6222033cd'), lower('0x29e38769f23701A2e4A8Ef0492e19dA4604Be62c') ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ) select substring(a.date, 1, 10) as date, 'polygon' as chain, -- a.transactions as in_transactions, -- b.transactions as out_transactions, -- in_transactions - out_transactions as net_transactions, a.users as in_users, b.users as out_users, in_users - out_users as net_users, a.volume as inflow, b.volume as outflow, inflow - outflow as netflow from inflow a left join outflow b on a.date = b.date ), avalanche as ( with inflow as ( select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct a.from_address ) as users, sum( amount_usd ) as volume from avalanche.core.ez_token_transfers a left join avalanche.core.fact_transactions b on a.tx_hash = b.tx_hash where origin_to_address in ( lower('0x45A01E4e04F14f7A4a6702c74187c5F6222033cd') ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ), outflow as ( select date_trunc('{{Date_Trunc}}', a.block_timestamp) as date, -- symbol, count( distinct b.tx_hash ) as transactions, count( distinct a.origin_to_address ) as users, sum( amount_usd ) as volume from avalanche.core.ez_avax_transfers a left join avalanche.core.fact_transactions b on a.tx_hash = b.tx_hash where eth_from_address in ( -- stable router lower('0x45A01E4e04F14f7A4a6702c74187c5F6222033cd'), lower('0x29e38769f23701A2e4A8Ef0492e19dA4604Be62c') ) and a.block_timestamp >= '{{Starting_Date}}' and a.block_timestamp <= '{{Ending_Date}}' group by 1 ) select substring(a.date, 1, 10) as date, 'avalanche' as chain, -- a.transactions as in_transactions, -- b.transactions as out_transactions, -- in_transactions - out_transactions as net_transactions, a.users as in_users, b.users as out_users, in_users - out_users as net_users, a.volume as inflow, b.volume as outflow, inflow - outflow as netflow from inflow a left join outflow b on a.date = b.date ) select * from arbitrum union all select * from ethereum union all select * from polygon union all select * from avalanche

Muze