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

'SEI' as symbol

, value[1] as price

from (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=90&interval=daily&precision=3') as resp

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

,

fee as ( select date(block_timestamp) as date,GAS_USED, block_id, tx_id, tx_from, split(fee,'usei') as fees , fees[0]/pow(10,6) as amount

from sei.core.fact_transactions

where block_timestamp::date >= '2023-08-15'

and tx_succeeded = 'true'

,

volume as ( select a.date,GAS_USED, tx_id, block_id, tx_from , amount, amount*price as fee_usd

from fee a join price b on a.date = b.date

,

final as (select count(DISTINCT(date)) as "Mainnet Days",

count(DISTINCT(block_id)) as "Blocks",

count(DISTINCT(tx_id)) as "Transactions",

count(DISTINCT(tx_from)) as "Users",

round("Blocks"/"Mainnet Days") as "Block/Day",

round("Transactions"/"Mainnet Days") as "Tx/Day",

round("Users"/"Mainnet Days") as "User/Day",

sum(fee_usd) as "Fee (USD)",

sum(amount) as "Fee (SEI)",

avg(fee_usd) as "Avg Fee (USD)",

avg(amount) as "Avg Fee (SEI)",

median(GAS_USED) as "Median Gas USED"

from volume)

,

blocks as (select count(DISTINCT block_timestamp::date) as days,