select BLOCK_TIMESTAMP::date as "Day" ,FROM_ADDRESS as "Swapper",'Arbitrum' as "Layer Name" from Arbitrum.core.fact_transactions where (TO_ADDRESS = lower('0xE592427A0AEce92De3Edee1F18E0157C05861564') or TO_ADDRESS = lower('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45')) and block_timestamp >= '2022-05-01'

),

op_swapper as (

select BLOCK_TIMESTAMP::date as "Day" ,FROM_ADDRESS as "Swapper",'Optimism' as "Layer Name" from Optimism.core.fact_transactions where (TO_ADDRESS = lower('0xE592427A0AEce92De3Edee1F18E0157C05861564') or TO_ADDRESS = lower('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45')) and block_timestamp >= '2022-05-01'

),

poly_swapper as (

select BLOCK_TIMESTAMP::date as "Day" ,FROM_ADDRESS as "Swapper",'Polygon' as "Layer Name" from Polygon.core.fact_transactions where (TO_ADDRESS = lower('0xE592427A0AEce92De3Edee1F18E0157C05861564') or TO_ADDRESS = lower('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45')) and block_timestamp >= '2022-05-01'

), all_uni_swaps as (

select * from arbi_swapper

union all

select * from op_swapper

union all

select * from poly_swapper

select

"Day",

sum(1) as "Daily Number of Swaps",

count(DISTINCT "Swapper") as "Number of Unique Swapper",

count(distinct (case when 'Optimism'="Layer Name" then "Swapper" end)) as "Unique Optimism Swapper",

count(distinct (case when 'Arbitrum'="Layer Name" then "Swapper" end)) as "Unique Arbitrum Swapper",

count(distinct (case when 'Polygon'="Layer Name" then "Swapper" end)) as "Unique Polygon Swapper",

count((case when 'Optimism'="Layer Name" then "Swapper" end)) as "Optimism Swap Count",

count((case when 'Arbitrum'="Layer Name" then "Swapper" end)) as "Arbitrum Swap Count",

count((case when 'Polygon'="Layer Name" then "Swapper" end)) as "Polygon Swap Count",

sum("Daily Number of Swaps") over (order by "Day") as "Cumulative Swap Count",

sum("Optimism Swap Count") over (order by "Day") as "Cumulative Optimism Swap Count",

sum("Arbitrum Swap Count") over (order by "Day") as "Cumulative Arbitrum Swap Count",

sum("Polygon Swap Count") over (order by "Day") as "Cumulative Polygon Swap Count"

from all_uni_swaps

group by "Day"

order by "Day"