Query latest pool of stake address from cardano db sync

Question

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

Answer 1

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

(...)