Calculate Nakamoto Coefficient for Chia

chia_nc.sql

select count(*)
from (select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS enumerator,
             puzzle_hash,
             percent,
             SUM(percent) OVER (ORDER BY percent desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as percent_sum
      from (select puzzle_hash, count(*) as blocks, count(*) / bb.block_count::float * 100 as percent
            from coin_records,
                 -- this is to get the total number of blocks in the last 7 days
                 (select count(*) as block_count
                  from coin_records
                  where coinbase = true
                    and created_at > current_timestamp - interval '7 days'
                    -- only count pool rewards to avoid 3rd part harvesters
                    and amount = 1750000000000) as bb
            where coinbase = true
              and created_at > current_timestamp - interval '7 days'
              -- only count pool rewards to avoid 3rd part harvesters
              and amount = 1750000000000
            group by puzzle_hash, bb.block_count
            order by blocks desc)
               as b
      order by percent desc) as c
where c.percent_sum < 50