SELECT

DATE_TRUNC('day', hour) AS date,

AVG(price) AS avg_price

FROM solana.price.ez_prices_hourly

WHERE TOKEN_ADDRESS = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So'

AND hour >= DATEADD(day, -30, CURRENT_DATE())

AND is_deprecated = FALSE

GROUP BY 1

),

liquid_staking AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS date,

SUM(CASE

WHEN action_type IN ('deposit', 'depositStakeAccount') THEN msol_minted

ELSE 0

END) AS msol_minted,

SUM(CASE

WHEN action_type = 'orderUnstake' THEN msol_burned

ELSE 0

END) AS msol_burned,

SUM(CASE

WHEN action_type = 'claim' THEN claim_amount

ELSE 0

END) AS sol_claimed,

COUNT(DISTINCT CASE

WHEN action_type IN ('deposit', 'depositStakeAccount')

THEN provider_address

END) AS mint_unique_users,

COUNT(DISTINCT CASE

WHEN action_type = 'orderUnstake'

THEN provider_address

END) AS burn_unique_users,

COUNT(DISTINCT CASE

WHEN action_type = 'claim'

THEN provider_address