Query editor
withprice_periods as (selecttimestamp as hour,price,lead(timestamp, 1, date_trunc('day', now())) over (order by timestamp) as next_hourfrom prices.daywhere blockchain = 'solana'and contract_address = from_base58('JUPyiwrYJFskUPiHa7hkeR8VUtAeFoSYbKedZNsDvCN')and timestamp > now() - interval '365' day),transfers as (selectfrom_owner,to_owner,amount / 1e6 as amount, -- JUP has 6 decimalsblock_time as timefrom tokens_solana.transferswhere token_mint_address = 'JUPyiwrYJFskUPiHa7hkeR8VUtAeFoSYbKedZNsDvCN'and block_time between now() - interval '365' day and now()),raw_delta as (select to_owner as account, amount, time from transfersunion allselect from_owner as account, -amount as amount, time from transfers),delta as (selectaccount, amount, price,time as start_time,lead(time, 1, now()) over (partition by account order by time) as end_time,sum(amount) over (partition by account order by time asc rows unbounded preceding) as cum_amount,sum(if(amount < 0, 1, 0)) over (partition by account order by time asc rows unbounded preceding) as realization_groupfrom raw_delta dinner join price_periods p on p.hour <= d.time and d.time < p.next_hour),raw_account_realized_cap as (selectaccount, start_time, end_time, cum_amount,sum(if(amount < 0, cum_amount, amount) * price) over (partition by account, realization_group order by start_time asc) as realized_account_cap,row_number() over (partition by account, start_time order by end_time desc) as rnfrom delta),account_realized_cap as (selectaccount, start_time, end_time,if(cum_amount > 0, cum_amount, 0) as cum_amount,if(realized_account_cap > 0, realized_account_cap, 0) as realized_account_capfrom raw_account_realized_capwhere rn = 1),days as (select cast(day as timestamp) as time_periodfrom unnest(sequence(current_date - interval '365' day, current_date, interval '1' day)) as t (day))selectdays.time_period,sum(realized_account_cap) / nullif(sum(cum_amount), 0) as realized_price,