1

How do i query latest pool of all the stake address when using cardano db sync?

0

The following will give you a list of pools with associated stake addresses.

  SELECT ph.view AS "pool_id", sa.view AS "stake_address"
  FROM stake_address sa
    JOIN stake_registration sr ON (sr.addr_id = sa.id)
    JOIN delegation d ON (d.addr_id = sa.id)
    JOIN pool_hash ph ON (ph.id = d.pool_hash_id)
  WHERE d.id = (
      SELECT MAX(id)
      FROM delegation
      WHERE addr_id = sa.id
    )
    AND sr.tx_id = (
      SELECT MAX(tx_id)
      FROM stake_registration
      WHERE addr_id = sa.id
    )
    AND (
      sr.tx_id > (
        SELECT COALESCE(MAX(tx_id), 0) -- handles IS NULL option so we don't have to run that query again
        FROM stake_deregistration
        WHERE addr_id = sa.id
      )
    )
    ORDER by pool_id;

Which will result.

                         pool_id                          |                        stake_address                        
----------------------------------------------------------+-------------------------------------------------------------
 pool100wj94uzf54vup2hdzk0afng4dhjaqggt7j434mtgm8v2gfvfgp | stake1u9nc4zs4wapwzg6lmrmc8uulngg5slphtd9zl8hzptsdhrcsph992
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1u9mmge8895vp5hsyutxuu0lhze5egj2ltszkqx9vtfwtc5c7llhe6
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1uykx3da7j9v0gp0ae2faxr27arlgfcrv4y980k0jgjemz8cy3m260
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1uxxqarsyyu6dt2c558qeza8fzwdsnqtkhdhts88gs8vy6sqfl3dq7
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1u8vygrzl9d3yfnf0scqx5vwe8yguyjyqljcrpy2tn0tq4pqffqv2d
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1uyp58nm4749e3dkdasyef52th308sytgx2j9yd7rl8c8efga46r7k
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1uxkxkdsehergfanuxu7p0rvdxkjflnllyhnjdrw7hudv5ush8nqlf
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1uxem4w0uuanqu0q9kedm250w0vfzfyylttf06kg4rn696jc6vj4kq
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1u9x3sppud2y3uztgzhq9qsq9wc8059fnqeaxelta92jjlmcneg6dg
 pool1026f3pyjhxjafmtjyyny5ytuh2kraxakfa8eu5h844uv2upzxvq | stake1ux5l7l829vjylmlmpj5jmlnwg9ryqatl5gjltvzlyhpq09qn874qt
 pool102kv4jtzs5cp2s58qa8esmdxc6awsu4a8smtlgkyylq7gvzgdwv | stake1uxzgxaygqmmus994yucrdwfgqg0ugn97pvlq7zeyzhhkhec8a2jm4
 pool102s2nqtea2hf5q0s4amj0evysmfnhrn4apyyhd4azcmsclzm96m | stake1u80u89wyha0ylkngfwdy9u9qermm64lpdfuy5uvjrfg8vvshahmgx

(...)

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.