Useful queries on `radix_ledger` DB

RadixQueries.sql

-- 1. Get balances of all accounts holding a token on a specific date
WITH new_year_state AS (
    select state_version
    from ledger_transactions
    where round_timestamp < '2022-01-09 00:00:00'
    order by round_timestamp desc
    limit 1
)
Select last_transaction, address, balance from
(select lt.round_timestamp as last_transaction, abh.to_state_version, abh.from_state_version, address,
        (cast(abh.balance as decimal) / pow(10, 18)) as balance,
        row_number() over (partition by address order by abh.from_state_version desc) as row_num
    from account_resource_balance_history abh
    LEFT join accounts a on a.id = abh.account_id
    LEFT join ledger_transactions lt on abh.from_state_version = lt.state_version

where abh.resource_id = (select id from resources where rri = 'dix_rr1qwne05jw2a42km0uk3xn5vrueqfrefdk3h06g9n6v4rqx54mwv')
and abh.from_state_version < (select state_version from new_year_state)
) balances
where row_num = 1
order by balance desc
limit 1000;