--Number and Volume of Transactions (abnormal transactions excluded)
with number_vol_tx as (
select date_trunc('day', block_timestamp) day,
count(*) no_of_tx
from ethereum.udm_events
where (FROM_LABEL='paraswap' OR TO_LABEL='paraswap') and
(tx_id!= lower('0x5bf790006eaac7c1eaa55fbf51f5ae6139b70e1492df1195caf9af38b77d45e3') and Symbol!='BICO' and BLOCK_TIMESTAMP!='2021-12-16 06:45:21.000')
group by day
order by day
),
psp_price_avg as (
select date_trunc('day',hour) day, avg(price) price from ethereum.token_prices_hourly
where token_address= lower ('0xcAfE001067cDEF266AfB7Eb5A286dCFD277f3dE5')
group by day order by day
)
select number_vol_tx.*, psp_price_avg.day daily_price, psp_price_avg.price psp_price_avg from number_vol_tx full join psp_price_avg on number_vol_tx.day=psp_price_avg.day