0

I am trying to write a query to find out stellar balances(for all addresses)but I am not able to get the correct operations to consider in that , maybe because I don't understand stellar very well .

So , the questions I have are as follows .

  1. How to calculate balance of an address with query. I have a very simple query but not giving correct balances . Here currency_id is for stellar .
SELECT 
 
  sum(in_value_xlm) total_in, 
  sum(out_value_xlm) total_out, 
  tx_date,
  total_in - total_out AS balance

  FROM 
  (
SELECT
   0 AS in_value_xlm,
   sum(toFloat64(value_from) / 10000000) AS out_value_xlm,
   tx_date
   
FROM stellar_flow.transfers_from
WHERE (transfer_from = '{{ addr }}') AND (currency_from_id = 417896) 
GROUP BY tx_date
UNION All 
SELECT
   sum(toFloat64(value_to) / 10000000) AS in_value_xlm,
   0 AS out_value_xlm,
   tx_date
FROM stellar_flow.transfers_to
WHERE (transfer_to = '{{ addr }}') AND (currency_to_id = 417896) 
GROUP BY tx_date
)
GROUP BY tx_date 

  1. Is there any website/page where I can find daily address stellar balance of an address , similar to stellar expert .

Thank you

2
  • Where is this data originating from? The schema doesn't look like anything standard, Is this a custom database you are using? Feb 17 at 18:16
  • Hey , Yes this is a custom database .I wanted to know what all operations to keep in mind for calculation for balances of an address
    – user5245
    Feb 18 at 5:08

1 Answer 1

0

If you have operation type included in your table, make sure to include the following operations, which impact the account balance:

  • 1 - payment
  • 2 - path_payment_strict_receive
  • 3 - manage_sell_offer, only once the trade executes
  • 4 - manage_buy_offer, only once the trade executes
  • 8 - account_merge
  • 13 - path_payment_strict_receive
  • 15 - claim_claimable_balance
  • 22 - liquidity_pool_deposit
  • 23 - liquidity_pool_withdraw

Stellar Expert is an excellent real time source for account balances; you can use the website or their Directory API. The Horizon API also fetches account balance details in real time.

If you want a daily dump of the data, you could use the publicly available GCP dataset to query the accounts table directly and create an extract.

1
  • Hello , thanks for the input. This definitely helps a lot .
    – user5245
    Feb 22 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.