with polygon as (with tab3 as (

with tab1 as (select date_trunc('day',block_timestamp) as date, sum(amount_in_usd) as selling_volume_usd, sum(amount_in) as selling_volume,

count(distinct tx_hash) as selling_count, count(distinct origin_from_address) as seller_count

from polygon.defi.ez_dex_swaps

where token_in=lower('0x4e78011ce80ee02d2c3e649fb657e45898257815')

group by 1),

tab2 as (select date_trunc('day',block_timestamp) as date, sum(amount_out_usd) as buying_volume_usd, sum(amount_out) as buying_volume,

count(distinct tx_hash) as buying_count

, count(distinct origin_from_address) as buyer_count

from polygon.defi.ez_dex_swaps

where token_out=lower('0x4e78011ce80ee02d2c3e649fb657e45898257815')

group by 1)

select tab1.date as "Date",

case when selling_volume_usd is null then 0 else selling_volume_usd end as selling_vol_usd,

case when buying_volume_usd is null then 0 else buying_volume_usd end as buying_vol_usd,

case when selling_volume is null then 0 else selling_volume end as selling_vol,

case when buying_volume is null then 0 else buying_volume end as buying_vol,

case when selling_count is null then 0 else selling_count end as selling_count,

case when buying_count is null then 0 else buying_count end as buying_count,

case when seller_count is null then 0 else seller_count end as seller_count,

case when buyer_count is null then 0 else buyer_count end as buyer_count

from tab1 left join tab2 on tab1.date=tab2.date

where tab1.date>='{{Start_Date}}' and tab1.date<='{{End_Date}}')

select "Date", selling_vol_usd as "Selling Volume ($USD)", buying_vol_usd as "Buying Volume ($USD)",

selling_vol_usd+buying_vol_usd as "Swap Volume ($USD)", buying_vol_usd-selling_vol_usd as "Net Volume ($USD)",

selling_vol as "Selling Volume ($KLIMA)", buying_vol as "Buying Volume ($KLIMA)",

selling_vol+buying_vol as "Swap Volume ($KLIMA)", buying_vol-selling_vol as "Net Volume ($KLIMA)",

selling_count as "Selling Count", buying_count as "Buying Count", selling_count+buying_count as "Swap Count",

seller_count as "Seller Count", Buyer_count as "Buyer Count", 'Polygon' as "Chain"

from tab3

order by 1),