c2ctrader03 - Airdrop Assessment - Number of transactions and PSP price
    Updated 2022-11-28
    --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
    Run a query to Download Data