prices as (

select

hour::date as price_date,

avg(price) as btc_price

from

bitcoin.price.ez_prices_hourly

group by

price_date

-- qualify

-- hour = last_value(hour) over (order by hour)

),

issuers as (

select

ft.value:TICKER as ticker,

ft.value:TYPE as type,

ft.value:ISSUER as issuer,

ft.value:CUSTODIAN as custodian,

ft.value:FEE as fee

from (

select

livequery.live.udf_api (

'https://flipsidecrypto.xyz/api/v1/queries/bc8abcd7-aa55-4ef9-83c1-1b7bf81313e0/data/latest'

):data as data

) lq,

table(flatten(input => lq.data)) ft

),

dates as (

select

date_day as date,

issuer,

ticker

from