Subtle changes, bugs and feedback

Thanks. The new stuff is pretty nice, and I've got to play with some awesome SQL recently... such as this beauty:

SELECT microcosm_id
     ,row_number() OVER(
          partition BY site_id
          ORDER BY count DESC, microcosm_id
      ) AS sequence
 FROM (
          SELECT m.site_id
                ,m.microcosm_id
                ,COALESCE(
                     (SELECT SUM(comment_count) + SUM(item_count)
                        FROM microcosms
                       WHERE path <@ m.path
                         AND is_deleted IS NOT TRUE
                         AND is_moderated IS NOT TRUE
                     ),
                     0
                 ) AS count
            FROM microcosms m
           GROUP BY m.site_id, m.microcosm_id
           ORDER BY site_id, count DESC
      ) AS mm

Isn't that great!

It has:

• row_number()
  
• OVER()
  
• PARTITION
  
• COALESCE
  
• An LTREE path
  
  

And what does it do?

It figures out the order in which to show all forums on all sites by rolling up the SUM of all comments and items in all forums, across the tree, and then ordering them by that total within each site, and then numbering them 1, 2, 3... within each site.

It's fun.