eth_price AS

SELECT

date_trunc( 'day' , a.hour ) AS day

, avg( a.price ) AS price

FROM

ethereum.core.fact_hourly_token_prices a

WHERE

a.symbol = 'WETH'

GROUP BY 1

ORDER BY 1 DESC

SELECT

date_trunc( 'day' , a.block_timestamp ) AS "date"

, count( DISTINCT a.from_address ) AS "players"

, count( DISTINCT a.tx_hash ) AS "play count"

, sum( a.eth_value * b.price ) AS "wagered"

, sum( "players" ) over ( ORDER BY "date" ASC ) AS "total players"

, sum( "play count" ) over ( ORDER BY "date" ASC ) AS "total play count"

, sum( "wagered" ) over ( ORDER BY "date" ASC ) AS "total wagered"