avax AS

SELECT

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

, avg( a.price ) AS pr

, a.symbol AS sb

, a.decimals AS de

FROM

avalanche.price.ez_hourly_token_prices a

WHERE

a.token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'

GROUP BY 1 , 3 , 4

ORDER BY 1 DESC

,

market_sale_1 AS

SELECT

b.block_timestamp AS dt

, b.tx_hash AS tx

, cast( b.decoded_log:"taker" AS string ) AS bu

, cast( b.decoded_log:"maker" AS string ) AS se

, cast( b.decoded_log:"erc721TokenId" AS string ) AS id

, ( ( cast( b.decoded_log:"erc20TokenAmount" AS integer ) / power( 10 , c.de ) ) * c.pr ) AS am

FROM

avalanche.nft.ez_nft_transfers a

LEFT JOIN

avalanche.core.ez_decoded_event_logs b

ON a.tx_hash = b.tx_hash

LEFT JOIN

avax c

ON a.block_timestamp::date = c.dt