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(c.block_timestamp) as date,GAS_USED,label, c.block_id, c.tx_id, tx_from, split(fee,'usei') as fees , fees[0]/pow(10,6) as amount

from sei.core.fact_msg_attributes a join sei.core.dim_labels b on a.attribute_value = b.address

join sei.core.fact_transactions c on a.tx_id = c.tx_id

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

and a.tx_succeeded = 'true'

and label_type not in ('operator','cex')

,

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 block_id as block,

count(DISTINCT(tx_id)) as tx,

count(DISTINCT(tx_from)) as users,

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(fee_usd) as "Median Fee (USD)",

median(amount) as "Median Fee (SEI)",

min(fee_usd) as "Min Fee (USD)",

min(amount) as "Min Fee (SEI)",

max(amount) as "Max Fee (SEI)",

max(fee_usd) as "Max Fee (USD)",