with price as (

select hour::date as date,

'ETH' as token,

avg(price) as eth_price

from ethereum.price.fact_hourly_token_prices

where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

group by 1,2

, s_ as ( -- swaps

SELECT

date_trunc('day', block_timestamp) as date,

case when block_timestamp::date >= '2021-03-01' then 'After Visa'

else 'Before Visa' end as type,

avg(eth_price) as eth_price,

count(distinct tx_hash) as swap_count,

sum(case when token_out = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

then amount_out end) as buying_volume,

-1*sum(case when token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

then amount_in end) as selling_volume,

sum(case when token_out = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

then amount_out end) as buying_pressure,

sum(case when token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

then amount_in end) as selling_pressure

FROM ethereum.defi.ez_dex_swaps

join price on (block_timestamp::date = date)

WHERE block_timestamp::date BETWEEN '2021-02-01' and '2021-04-01'

and (token_out = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

or token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48')

GROUP BY 1,2

*, buying_volume + selling_volume as diff,

sum(diff) over (order by date) as "Net USDC Volume"