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, address_name, 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 between (current_date - 31) and (current_date - 1)

and c.block_timestamp between (current_date - 31) and (current_date - 1)

and a.tx_succeeded = 'true'

and label_type not in ('cex')

,

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

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

select tx_from as user,

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

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

count(DISTINCT(label)) as "Projects",

count(DISTINCT(address_name)) as "Contracts",

round("Transactions"/"Mainnet Days") as "Tx/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(fee_usd) as "Median Fee (USD)",

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