SELECT date_trunc('day',HOUR) as date ,

avg(price) as Op_price

FROM optimism.core.fact_hourly_token_prices

WHERE token_address = '0x4200000000000000000000000000000000000042' --- Op contract

GROUP BY 1

),

ETH AS (

SELECT date_trunc('day',HOUR) as date ,

avg(price) as ETH_price

FROM ethereum.core.fact_hourly_token_prices

WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH contract

GROUP BY 1

),

seles AS (

SELECT date_trunc('day',block_timestamp) AS date1,

COUNT(DISTINCT tx_hash)AS sale_count ,

COUNT(DISTINCT buyer_address) AS unique_buyers ,

sum(

CASE WHEN currency_address = 'ETH' THEN price * ETH_price

WHEN currency_address = '0x4200000000000000000000000000000000000042' THEN price * Op_price END

) AS volume ,

sum(sale_count) over (ORDER BY date1) AS cumu_count,

sum(unique_buyers) over (ORDER BY date1) AS cumu_buyer,

sum(volume) over (ORDER BY date1) AS cumu_volume,

sale_count/datediff(day,'2021-12-18' , current_date-1) AS average_sale_day,

unique_buyers/datediff(day,'2021-12-18' , current_date-1) AS average_unique_buyers_day,

volume/datediff(day,'2021-12-18' , current_date-1) AS average_volume_day,

volume/sale_count AS "average selling price (ASP) ",

avg(

CASE WHEN currency_address = 'ETH' THEN price * ETH_price

WHEN currency_address = '0x4200000000000000000000000000000000000042' THEN price * Op_price END

) as avg_volume,

avg(avg_volume) OVER (ORDER BY date1 ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS MA_weekly,