main as (

select

ORIGIN_FROM_ADDRESS as claimer ,

RAW_AMOUNT/pow(10,18) as volume ,

(PRICE * RAW_AMOUNT/pow(10,18) )as USD ,

BLOCK_TIMESTAMP ,

TX_HASH

from optimism.core.fact_token_transfers join optimism.core.fact_hourly_token_prices

on LOWER(TOKEN_ADDRESS) = LOWER(CONTRACT_ADDRESS )

where

ORIGIN_TO_ADDRESS ='0xfedfaf1a10335448b7fa0268f56d2b44dbd357de' and

CONTRACT_ADDRESS = '0x4200000000000000000000000000000000000042' and

FROM_ADDRESS ='0xfedfaf1a10335448b7fa0268f56d2b44dbd357de' and

ORIGIN_FUNCTION_SIGNATURE ='0x2e7ba6ef' and

DATE_TRUNC('HOUR', HOUR) = DATE_TRUNC('HOUR',BLOCK_TIMESTAMP)

select

DATE_TRUNC('month', BLOCK_TIMESTAMP) AS DATE ,

DATE_TRUNC('month', BLOCK_TIMESTAMP) AS DATE1 ,

count(distinct TX_HASH) as trxs ,

count(distinct claimer) as claimers ,

sum(volume) as OP ,

avg(volume) as avg_OP ,

median(volume) as median_OP ,

max(volume) as maxx_OP ,

min(volume) as minn_OP ,

sum(USD) as USD ,

avg(USD) as avg_USD ,

median(USD) as median_USD ,

max(USD) as maxx_USD ,

min(USD) as minn_USD ,

sum(trxs) over (order by date ) as cum_trxs ,

sum(claimers) over (order by date ) as cum_claimers ,

sum(OP) over (order by date ) as cum_OP ,