select TIMESTAMP::date as date, avg(price_usd) as flowprice

from flow.core.fact_prices

where symbol ='FLOW' and source ='coinmarketcap'

group by 1),

base as (

SELECT

*

FROM near.core.fact_transactions

WHERE 1=1

AND (tx_receiver LIKE '%paras%')

),

extracts as (

SELECT

tx_hash, block_timestamp,

--logs,

replace(logs[0], '\\') as logs_pre,

CASE WHEN logs_pre like 'EVENT_JSON:%' THEN replace( replace(split(logs_pre,'EVENT_JSON:')[1], '\:\"\{\"', '\:\{\"'), '\"}\"\}', '\"}\}') ELSE logs_pre END as logs_cleaned,

check_json(logs_cleaned) as checks,

TRY_PARSE_JSON(logs_cleaned) as log_json

FROM near.core.fact_receipts

WHERE 1=1

AND tx_hash IN (SELECT tx_hash from base)

AND ARRAY_SIZE(logs)>0

),

types as (

SELECT

e.tx_hash,

e.block_timestamp,

tx_signer,

tx_receiver,

coalesce(log_json:type, log_json:event) as type,