WITH flattened AS (

SELECT

tx_id,

block_timestamp,

f.index as array_index,

f.value as account_key,

post_balances[f.index] as post_balance,

pre_balances[f.index] as pre_balance,

ABS(post_balances[f.index] - pre_balances[f.index]) as balance_change

FROM eclipse.core.fact_transactions t,

LATERAL FLATTEN(input => ACCOUNT_KEYS) f

where ACCOUNT_KEY['pubkey'] like '8ZoX5t72zSvMPzqfoHNoRChzWf5pHvYi43GXYcZ1qZL2'

and block_timestamp > '2024-11-10'

and SUCCEEDED

sum(fees_usd) as fees_usd

FROM (

SELECT

tx_id,

block_timestamp,

array_index,

account_key,

post_balance / pow(10, 9) as post_balance_sol,

pre_balance / pow(10, 9) as pre_balance_sol,

balance_change / pow(10, 9) as balance_change_sol,

balance_change_sol * price as fees_usd

FROM flattened

left outer join (

SELECT

hour as h1,

price