with btc_price as (

SELECT date_trunc('day',hour) as day,

avg(price) as btc_price

FROM bitcoin.price.ez_hourly_token_prices

group by 1 )

SELECT date_trunc('day', BLOCK_TIMESTAMP) AS DATE

-- ,CASE

-- WHEN cast(hex AS VARCHAR) LIKE '%696d6167652f6a706567%' -- jpeg

-- OR cast(hex AS VARCHAR) LIKE '%696d6167652f706e67%' -- png

-- OR cast(hex AS VARCHAR) LIKE '%696d6167652f676966%' -- gif

-- OR cast(hex AS VARCHAR) LIKE '%696d6167652f77656270%' -- webp

-- OR cast(hex AS VARCHAR) LIKE '%696d6167652f7376672b786d6c%' -- svg+xml

-- OR cast(hex AS VARCHAR) LIKE '%696d6167652f61766966%' -- avif

-- OR cast(hex AS VARCHAR) LIKE '%696d6167652f61706e67%' -- apng

-- THEN 'image'

-- WHEN cast(hex AS VARCHAR) LIKE '%766964656f2f6d7034%' --mp4

-- OR cast(hex AS VARCHAR) LIKE '%766964656f2f7765626d%' --webm

-- THEN 'video'

-- WHEN cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f6f637465742d73747265616d%' --octet-stream

-- OR cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f657075622b7a6970%' --epub+zip

-- OR cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f6a736f6e%' --json

-- OR cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f706466%' --pdf

-- OR cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f6a617661736372697074%' --javascript

-- OR cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f7067702d7369676e6174757265%' --pgp-signature

-- OR cast(hex AS VARCHAR) LIKE '%6170706c69636174696f6e2f79616d6c%' --yaml

-- THEN 'application'

-- WHEN cast(hex AS VARCHAR) LIKE '%617564696f2f6d696469%' --midi

-- OR cast(hex AS VARCHAR) LIKE '%617564696f2f6d706567%' --mpeg

-- OR cast(hex AS VARCHAR) LIKE '%617564696f2f6d6f64%' --mod

-- OR cast(hex AS VARCHAR) LIKE '%617564696f2f666c6163%' --flac

-- OR cast(hex AS VARCHAR) LIKE '%617564696f2f776176%' --Wav

-- THEN 'audio'

-- WHEN cast(hex AS VARCHAR) LIKE '%746578742f68746d6c3b636861727365743d7574662d38%' --html;charset=utf-8