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)

,

dapps as ( select date(block_timestamp) as date, tx_id, label_type, label, address_name,

case when label ilike '%blockus%' then 'Game'

when label ilike '%yolee%' then 'Game'

when label ilike '%Gafin%' then 'Game'

when label ilike '%fable%' then 'Game'

when label ilike '%Entice%' then 'Game'

when label ilike '%double%' then 'Game'

when label ilike '%fusy%' then 'Game'

when label ilike '%Gelotto%' then 'Game'

when label ilike '%mystic%' then 'Game'

when label ilike '%tatami%' then 'Game' else label_type end as label_types

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

where TX_SUCCEEDED = 'true'

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

and block_timestamp >= current_date - 31)

,

dapps_tx as ( select date, a.tx_id, label_types, label, address_name , tx_from, split(fee,'usei') as fees , fees[0]/pow(10,6) as fee_amount

from sei.core.fact_transactions a join dapps b on a.tx_id = b.tx_id )

,

volume as ( select a.date, a.tx_id, label_types, label, address_name , tx_from, fee_amount, fee_amount*price as fee_usd

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

select trunc(date,'week') as weekly, label_types as type, count(DISTINCT(tx_from)) as users, count(DISTINCT(tx_id)) as total_tx,

sum(fee_usd) as fees_usd, avg(fee_usd) as avg_fee_usd,