with base_contact_address as (select '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' as token_address union all select '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270'as token_address union all select '0x42e5e06ef5b90fe15f853f59299fc96259209c5c'as token_address union all select '0x539bde0d7dbd336b79148aa742883198bbf60342'as token_address union all select '0xb0c7a3ba49c7a6eaba6cd4a96c55a1391070ac9a'as token_address union all select '0xf97f4df75117a78c1a5a0dbb814af92458539fb4'as token_address union all select '0x514910771af9ca656af840dff83e8264ecf986ca'as token_address union all select '0xb5c622c8598c5f69d48dea1ea414189502e6b35d'as token_address union all select '0x403e967b044d4be25170310157cb1a4bf10bdd0f'as token_address union all select '0x74b23882a30290451a17c44f4f05243b6b58c76d'as token_address union all select '0x2170ed0880ac9a755fd29b2688956bd959f933f8'as token_address union all select '0x6a3e7c3c6ef65ee26975b12293ca1aad7e1daed2'as token_address union all select '0x44a6e0be76e1d9620a7f76588e4509fe4fa8e8c8'as token_address union all select '0x0e09fabb73bd3ade0a17ecc321fd13a19e81ce82'as token_address union all select '0xe06bd4f5aac8d0aa337d13ec88db6defc6eaeefe'as token_address union all select '0xb3654dc3d10ea7645f8319668e8f54d2574fbdc8'as token_address union all select '0xc748b2a084f8efc47e086ccddd9b7e67aeb571bf'as token_address union all select '0xd1ba9bac957322d6e8c07a160a3a8da11a0d2867'as token_address union all select '0x218532a12a389a4a92fc0c5fb22901d1c19198aa'as token_address union all select '0x6c2c06790b3e3e3c38e12ee22f8183b37a13ee55'as token_address union all select '0x6b23c89196deb721e6fd9726e6c76e4810a464bc'as token_address union all select '0x53e0bca35ec356bd5dddfebbd1fc0fd03fabad39'as token_address union all select '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'as token_address union all select '0x0000000000004946c0e9f43f4dee607b0ef1fa1c'as token_address union all select '0xba2ae424d960c26247dd6c32edc70b295c744c43'as token_address union all select '0xf8a0bf9cf54bb92f17374d9e9a321e6a111a51bd'as token_address union all select '0x7130d2a12b9bcbfae4f2634d864a1ee1ce3ead9c'as token_address union all select '0xeec2be5c91ae7f8a338e1e5f3b5de49d07afdc81'as token_address union all select '0xf2ae0038696774d65e67892c9d301c5f2cbbda58'as token_address union all select '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619'as token_address union all select '0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f'as token_address union all select '0x82af49447d8a07e3bd95bd0d56f35241523fbab1'as token_address union all select '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'as token_address union all select '0x1bfd67037b42cf73acf2047067bd4f2c47d9bfd6'as token_address union all select '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c'as token_address union all select '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'as token_address union all select '0x21be370d5312f44cb42ce377bc9b8a0cef1a4c83'as token_address union all select '0xb6ee9668771a79be7967ee29a63d4184f8097143'as token_address union all select '0x20f663cea80face82acdfa3aae6862d246ce0333'as token_address ), base_timeframe as (select date(dateadd('DAY', -35, getdate())) as start_date, date(dateadd('DAY', -1, getdate())) as end_date), base_bsc as (select 'BSC' as blockchain, fel.contract_address, fel.tx_hash, fel.event_name, fel.tx_status from bsc.core.fact_event_logs fel where event_name is not null and event_name not in ('Approval', 'ExecutionSuccess') and tx_hash in (select distinct tx_hash from bsc.core.fact_event_logs where date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) and contract_address in (select * from base_contact_address)) and date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) qualify row_number() over(partition by tx_hash order by event_index desc) = 1), base_polygon as (select 'Polygon' as blockchain, fel.contract_address, fel.tx_hash, fel.event_name, fel.tx_status from polygon.core.fact_event_logs fel where event_name is not null and event_name not in ('Approval', 'ExecutionSuccess') and tx_hash in (select distinct tx_hash from polygon.core.fact_event_logs where date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) and contract_address in (select * from base_contact_address)) and date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) qualify row_number() over(partition by tx_hash order by event_index desc) = 1), base_ethereum as (select 'Ethereum' as blockchain, fel.contract_address, fel.tx_hash, fel.event_name, fel.tx_status from ethereum.core.fact_event_logs fel where event_name is not null and event_name not in ('Approval', 'ExecutionSuccess') and tx_hash in (select distinct tx_hash from ethereum.core.fact_event_logs where date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) and contract_address in (select * from base_contact_address)) and date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) qualify row_number() over(partition by tx_hash order by event_index desc) = 1), base_arbitrum as (select 'Arbitrum' as blockchain, fel.contract_address, fel.tx_hash, fel.event_name, fel.tx_status from arbitrum.core.fact_event_logs fel where event_name is not null and event_name not in ('Approval', 'ExecutionSuccess') and tx_hash in (select distinct tx_hash from arbitrum.core.fact_event_logs where date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) and contract_address in (select * from base_contact_address)) and date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) qualify row_number() over(partition by tx_hash order by event_index desc) = 1), base_avalanche as (select 'Avalanche' as blockchain, fel.contract_address, fel.tx_hash, fel.event_name, fel.tx_status from avalanche.core.fact_event_logs fel where event_name is not null and event_name not in ('Approval', 'ExecutionSuccess') and tx_hash in (select distinct tx_hash from avalanche.core.fact_event_logs where date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) and contract_address in (select * from base_contact_address)) and date(block_timestamp) >= (select start_date from base_timeframe) and date(block_timestamp) <= (select end_date from base_timeframe) qualify row_number() over(partition by tx_hash order by event_index desc) = 1), base_final as (select * from base_bsc union all select * from base_polygon union all select * from base_ethereum union all select * from base_arbitrum union all select * from base_avalanche), base_summ as (select blockchain, case when lower(event_name) like '%swap%' then 'Swap' else event_name end as event_name, count(distinct tx_hash) as transactions from base_final a where lower(tx_status) = 'success' group by blockchain, event_name) select * from base_summ order by transactions desc

paluvadisurya-Xm2IvE