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)),

bd_transfers as (

select BLOCK_TIMESTAMP, from_address, to_address, amount_raw, amount_raw/pow(10,24) as amount, tx_hash

from (

select l.value::STRING as str,

split(str,' ')[1] as amount_raw,

split(str,' ')[3]::string as from_address,

split(str,' ')[5]::string as to_address, a.*

from near.core.fact_receipts a,

LATERAL FLATTEN(INPUT => a.logs) l

where RECEIVER_ID='blackdragon.tkn.near'

and (str ilike 'Transfer % from % to %' or str ilike 'Refund % from % to %')