pubkey_script_address as address,

sum(value) as balance,

sum(datediff('day', o.block_timestamp, current_timestamp)) as age_datediff

-- extract(day from sum(value * datediff(day, current_timestamp, o.block_timestamp)) / sum(value)) as weighted_age_utxo_days

bitcoin.core.fact_outputs o

not exists (

select 1

from bitcoin.core.fact_inputs i

where i.spent_tx_id = o.tx_id

) -- ignore UTXOs that have been spent already

and pubkey_script_type != 'nulldata' -- could also check script_asm NOT LIKE '%OP_RETURN%'

and pubkey_script_address is not null -- bunch of undecoded scripts right now that we're going to ignore.

group by

1

order by

2 desc

100