SELECT

TO_TIMESTAMP(value[0]::string) as date,

value[1] as price

FROM (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=max&interval=daily&precision=3') as response

),LATERAL FLATTEN (input => response:data:prices)

,

main as (

SELECT

block_timestamp,

STATUS,

tx_hash,

block_number,

tx_fee,

tx_fee * b.price as tx_fee_usd

FROM blast.core.fact_transactions a

LEFT JOIN TokenPrices b ON date_trunc('d', block_timestamp) = b.date

SELECT

date_trunc('{{granularity}}',block_timestamp) as date,

count(DISTINCT tx_hash) as n_txs,

sum(CASE WHEN STATUS = 'SUCCESS' THEN 1 ELSE 0 END) as successful_txns,

sum(CASE WHEN STATUS != 'SUCCESS' THEN 1 ELSE 0 END) as unsuccessful_txns,

100*successful_txns/(successful_txns+unsuccessful_txns) as success_rate,

sum(n_txs) over (ORDER BY date) as cum_txs,

count(DISTINCT block_number) as block_number_count,

sum(block_number_count) over (ORDER BY date) as cum_block,

sum(tx_fee) as "Gas Fee [ETH]",

avg(tx_fee) as "Avg. Gas Fee [ETH]",

avg(tx_fee_usd) as "Avg .Gas Fee [USD]",

median(tx_fee) as "Median Gas Fee [ETH]",

median(tx_fee_usd) as "Median Gas Fee [USD]",