with panicked_txs as (

select tx_hash

from near.core.fact_receipts

where STATUS_VALUE:Failure is not null

and block_timestamp::date>'2023-10-28'

group by 1),

succeeded_txs as (

select tx_hash as tx

from near.core.fact_transactions

where tx_succeeded=true

and block_timestamp::date>'2023-10-28'

-- and tx_hash not in (select tx_hash from panicked_txs)

),

price as (

select

value[0]::string as hour,

value[1]::float as price

from (select livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/7ea1cb32-d9b9-4f97-89f2-ae4fda842f00/latest-run')

as response), lateral FLATTEN (input => response:data:result:rows)),

date_start as (

with dates AS (

SELECT CAST('2023-10-28' AS DATE) AS start_date

UNION ALL

SELECT DATEADD(day, 1, start_date)

FROM dates

WHERE start_date < CURRENT_DATE())

SELECT date_trunc(day, start_date) AS start_date

FROM dates),