SELECT

'FLOW' as BLOCKCHAIN,

TX_ID,

BLOCK_TIMESTAMP,

DELEGATOR,

CASE WHEN ACTION IN ('DelegatorTokensCommitted', 'TokensCommitted') then 'Stake'

WHEN ACTION IN ('DelegatorUnstakedTokensWithdrawn', 'UnstakedTokensWithdrawn') then 'Unstake'

ELSE 'NA' END AS STAKE_TYPE,

AMOUNT as STAKED_FLOW,

AMOUNT * FLOW_PRICE as STAKED_USD,

NODE_ID

FROM

flow.core.ez_staking_actions a JOIN (SELECT RECORDED_HOUR::date as date, AVG(CLOSE) as FLOW_PRICE FROM flow.core.fact_hourly_prices WHERE ID = 'flow' GROUP BY 1) b

ON a.BLOCK_TIMESTAMP::date = b.date

WHERE

TX_SUCCEEDED = 'true'

UNION ALL

SELECT

'OSMOSIS' as BLOCKCHAIN,

TX_ID,

BLOCK_TIMESTAMP,

DELEGATOR_ADDRESS,

CASE WHEN ACTION IN ('delegate') then 'Stake'

WHEN ACTION IN ('undelegate') then 'Unstake'

ELSE 'NA' END AS STAKE_TYPE,

AMOUNT / POW(10, DECIMAL) as STAKED_CURRENCY,

STAKED_CURRENCY * PRICE as STAKED_USD,

VALIDATOR_ADDRESS

FROM

osmosis.core.fact_staking a JOIN osmosis.core.ez_prices b ON (a.BLOCK_TIMESTAMP::date = b.RECORDED_HOUR::date AND a.CURRENCY = b.CURRENCY)

UNION ALL