Nice info we are getting closer... if this better I could start using this part of the tables.
Only now i'm missing the delegated do you know why that is not part of the VESTS balance?
WITH nai_map (nai, asset_name) AS (
VALUES
(21, 'HIVE'),
(13, 'HBD'),
(37, 'VESTS')
)
SELECT
a.name AS account_name,
x.updated_at,
x.source_op,
x.source_op_block,
-- nm.asset_name,
-- x.balance,
/* Convert to HIVE only when the asset is VESTS */
CASE
WHEN nm.asset_name = 'VESTS'
THEN hafsql.vests_to_hive(x.balance/1e6, a.block_num)
ELSE x.balance/1e3
END AS balance,
/* The unit of balance_value */
CASE
WHEN nm.asset_name = 'VESTS' THEN 'HP'
ELSE nm.asset_name
END AS asset,
s.balance AS saving
FROM hafbe_bal.balance_history_by_month x
JOIN hafd.accounts a
ON x.account = a.id
LEFT JOIN hafbe_bal.saving_history_by_month s
ON s.account = x.account
AND s.updated_at = x.updated_at
AND s.nai = x.nai
LEFT JOIN nai_map nm
ON x.nai = nm.nai
WHERE a.name = 'beaker007'
ORDER BY x.updated_at DESC;
There is no historical tracking of delegations. There are only current values available.
Oke clear. you cannot have everything 😁 some limitation over performance that is the question.
@azricon, how important do think it is to include the delegations?
I can take a look later to see if I can come up with a query that tracks historical delegations and make that a materialized view which can refresh once an hour or so depending on the query.