(SELECT

AVG(PRICE) AS Price

, HOUR::DATE AS Date

FROM

crosschain.price.ez_prices_hourly

WHERE

TOKEN_ADDRESS = 'ibc/785AFEC6B3741100D15E7AF01374E3C4C36F24888E96479B1C33F5C71F364EF9'

GROUP BY 2),

STAKE AS

(SELECT

BLOCK_TIMESTAMP::DATE AS Date

, SUM(CASE WHEN ACTION ='undelegate' THEN ((AMOUNT * -1) /1E6) ELSE 0 END) AS Unstake

, SUM(CASE WHEN ACTION !='undelegate' THEN AMOUNT/1E6 ELSE 0 END) AS Stake

, Stake + Unstake AS Net_flow

, SUM(Net_flow) OVER (ORDER BY DATE) AS "Total staked"

FROM

terra.defi.ez_staking

GROUP BY 1)

SELECT

A.*

, UNSTAKE * PRICE AS UNSTAKE_USD

, STAKE * PRICE AS STAKE_USD

, NET_FLOW * PRICE AS NET_FLOW_USD

, "Total staked" * PRICE AS TOTAL_STAKED

FROM

STAKE A LEFT JOIN Price B ON A.Date=B.Date

ORDER BY A.DATE DESC