With swap_volume as (
select
date_trunc('day',block_timestamp) as date,
count(tx_id) as nb_swaps,
count(distinct(from_address)) as nb_of_unique_user,
sum(amount_usd) as swap_volume
from ethereum.dex_swaps
where block_timestamp >= '2022-01-01'
and block_timestamp < CURRENT_DATE() - 1
and platform = 'sushiswap'
and lower(direction) = 'out' -- to avoid double counting the amounts
and amount_usd < 3000000000 -- under 3B. There seems to be one erroneous data point. I filetered it out.
group by date),
SUSHI_price as (
Select
avg(price) as price,
date_trunc('day',hour) as date
from ethereum.token_prices_hourly
where lower(symbol) = 'sushi'
and date_trunc('day',hour) >= '2022-01-01'
and price > 1
group by 2
)
, final_table as (
Select
swap_volume.date as date,
swap_volume,
nb_swaps,
SUSHI_price.price as SUSHI_price
from swap_volume left join SUSHI_price on swap_volume.date = SUSHI_price.date
Group by swap_volume.date,2,3,4
order by swap_volume.date desc
)
SELECT