address,

version,

change_data:coin:value::INT/1e8 as apt_balance

FROM aptos.core.fact_changes

WHERE DATE(block_timestamp) = '2024-09-07'

AND change_type = 'write_resource'

AND address = '0x34ca84470e8d2907562f9a2f144c6c780282953b8b025ba220b0ecc5fc0aead9'

AND inner_change_type = '0x1::coin::CoinStore<0x1::aptos_coin::AptosCoin>'

QUALIFY ROW_NUMBER() OVER (ORDER BY version DESC) = 1;

select * from

(SELECT

address,

-- version,

change_data:coin:value::INT/1e8 as apt_balance

FROM

aptos.core.fact_changes

WHERE

DATE(block_timestamp) = '2024-09-01'

AND change_type = 'write_resource'

AND inner_change_type = '0x1::coin::CoinStore<0x1::aptos_coin::AptosCoin>'

QUALIFY

ROW_NUMBER() OVER (PARTITION BY address ORDER BY version DESC) = 1)k

where

k.apt_balance > 0

order by k.apt_balance desc;

select * from

(SELECT

address,

version,

change_data:coin:value::INT/1e8 as apt_balance