RE: How SPS Stakeholders and Staking HP and HBD?

avatar

You are viewing a single comment's thread:

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;

image.png

hive-169191
0
0
0.000
5 comments
avatar

There is no historical tracking of delegations. There are only current values available.

0
0
0.000
Reply
avatar

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?

0
0
0.000
Reply
avatar

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.

0
0
0.000
Reply
avatar

Do not what to spam you 😂. But i found a another method to limit the call that i will commit for now

This does the first last record and every first of the month, exactly what is want

WITH bh AS (
  SELECT
    bh.*,
    hb.timestamp AS block_timestamp,
    date_trunc('month', hb.timestamp) AS month_start,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name
      ORDER BY bh.block_num ASC
    ) AS rn_first,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name
      ORDER BY bh.block_num DESC
    ) AS rn_last,
    ROW_NUMBER() OVER (
      PARTITION BY bh.account_name, date_trunc('month', hb.timestamp)
      ORDER BY hb.timestamp ASC, bh.block_num ASC
    ) AS rn_month
  FROM hafsql.balances_history bh
  JOIN hafsql.haf_blocks hb
    ON hb.block_num = bh.block_num
  WHERE bh.account_name IN ({placeholders})
)
SELECT
  *
FROM bh
WHERE rn_first = 1
   OR rn_last  = 1
   OR rn_month = 1
ORDER BY account_name, block_num DESC;

limited the number of account at once to 5

Thanks again for the help 🙏

0
0
0.000
Reply
avatar

You are not spamming. This one takes too long for top witnesses.

0
0
0.000
Reply