SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/msol/market_chart?vs_currency=usd&days=365'

) AS market_data

),

sol_data AS (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/solana/market_chart?vs_currency=usd&days=365'

) AS market_data

),

msol_prices AS (

SELECT

DATEADD('millisecond', data.value[0]::bigint, '1970-01-01 00:00:00')::DATE AS date,

data.value[1]::FLOAT AS price_usd

FROM msol_data, LATERAL FLATTEN(input => market_data:data:prices) data

),

sol_prices AS (

SELECT

DATEADD('millisecond', data.value[0]::bigint, '1970-01-01 00:00:00')::DATE AS date,

data.value[1]::FLOAT AS price_usd

FROM sol_data, LATERAL FLATTEN(input => market_data:data:prices) data

),

latest_sol_per_msol AS (

SELECT

sol_prices.price_usd / msol_prices.price_usd AS sol_to_msol

FROM sol_prices

JOIN msol_prices ON sol_prices.date = msol_prices.date

ORDER BY sol_prices.date DESC

LIMIT 1

),

staker_data AS (

SELECT

PROVIDER_ADDRESS AS staker,

SUM(DEPOSIT_AMOUNT) AS total_staked_msol

FROM solana.marinade.ez_liquid_staking_actions

WHERE ACTION_TYPE IN ('deposit', 'depositStakeAccount')