SELECT

recorded_hour::date as p_date,

avg(close) as price

FROM solana.core.fact_token_prices_hourly

WHERE symbol LIKE 'HONEY'

and id LIKE 'hivemapper'

GROUP BY 1

SELECT

date_trunc('week', block_timestamp) as date,

count(DISTINCT tx_id) as tx_cnt,

count(DISTINCT instructions[0]:accounts[2]) as reward_receivers,

sum(PARSE_JSON(inner_instructions[0]:instructions[1]:parsed:info:amount::int) / POW(10, 9)) as reward_amt,

sum((PARSE_JSON(inner_instructions[0]:instructions[1]:parsed:info:amount::int) / POW(10, 9))*price) as reward_amt_usd,

sum(tx_cnt) over (order by date) as cum_tx_cnt,

sum(reward_amt) over (order by date) as cum_reward_amt,

sum(reward_amt_usd) over (order by date) as cum_reward_amt_usd

FROM solana.core.fact_transactions t

JOIN PriceTb on p_date = t.block_timestamp::date

WHERE instructions[0]:programId = 'BNH1dUp3ExFbgo3YctSqQbJXRFn3ffkwbcmSas8azfaW'

AND PARSE_JSON(inner_instructions[0]:instructions[1]:parsed:info:amount::int) / POW(10, 9) > 0

AND block_timestamp >= current_date() - 180

GROUP by 1

order by 1