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-01'

GROUP BY

1,

2

),

claim_reward as (

SELECT

date_trunc('hour', block_timestamp) as hour,

tx_id,

tx_to as user_address,

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 = 'BEYJqFx5G6whGmpJ4Ar9yyKfnDVBTcGWDSnJPPmsPWh6'

AND mint IN (

'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v',

'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263',

'CPwspzHc4bKtBQGNRhpRG9v3qRiPLWP28GrfZepwmBSz'