SELECT

recorded_hour as hour,

CASE

when symbol = 'HXRO' then 'esHXRO'

ELSE symbol

END AS symbol,

avg(close) as price

FROM

solana.price.fact_token_prices_hourly

WHERE

symbol IN ('USDC', 'HXRO', 'BONK')

AND recorded_hour >= '2022-10-04'

GROUP BY

1,

2

),

claim_reward as (

SELECT

date_trunc('hour', block_timestamp) as hour,

tx_id,

CASE

when mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then 'USDC'

when mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' then 'BONK'

when mint = 'CPwspzHc4bKtBQGNRhpRG9v3qRiPLWP28GrfZepwmBSz' then 'esHXRO'

END AS fee_reward,

amount

FROM

solana.core.fact_transfers

WHERE

--tx_from in(

--'74Y6DX9KEa13LhvbLRaib8weMhB2vYfKjXVzkKwvmHoR',

--'FoCmS48FRyJrx6bozDijaARYAThdUeUGu4rbGKqBegcH',

--'9GVgoW95wpxcYitniDcMQaupmuaCTQEfSyKDUuyW84Zh',

--'ChQnSYFJz72HXcUm1yUuX1491FLrKppC6eFxpj18W5y8',

--'AARaFgaGwAoZAGqwf8Kn5aqxpZvuDotMMH6HgckJpCC7',