PostgreSQL query select records with highest count

Question

I have a table we will call authors.

authors has many blog_posts and has many articles.

Both blog_posts and articles have a column state

I need to write a query that gets the top x amount of authors by the highest total blog_posts and articles combined where the state is equal to open

I've come up with this so far

WITH blog_post_count AS (
    SELECT author_id, count(id) as num_open_blog_posts
    FROM  blog_posts
    WHERE state = 'open'
    GROUP BY author_id
),
article_count AS (
    SELECT author_id, count(id) as num_open_articles
    FROM articles
    WHERE state = 'open'
    GROUP BY author_id
)
SELECT *, (
            CASE WHEN num_open_blog_posts IS NULL THEN 0 ELSE num_open_blog_posts END
            + CASE WHEN num_open_articles IS NULL THEN 0 ELSE num_open_articles END) as total_postings
    FROM authors
    LEFT OUTER JOIN blog_post_count ON blog_post_count.author_id = authors.id
    LEFT OUTER JOIN article_count ON article_count.author_id = authors.id 
    ORDER BY total_postings DESC
    LIMIT 25 -- could be any value

^works but I'm sure it could be improved alot

Answer 1

How about using UNION ALL ?

WITH total_post_count AS (
  SELECT 
    author_id
  FROM  
    blog_posts
  WHERE 
    state = 'open'
  UNION ALL
  SELECT 
    author_id
  FROM 
    articles
  WHERE 
    state = 'open'
)
SELECT 
  author_id,
  count(*) as total_posts
FROM 
  total_post_count
GROUP BY 
  author_id
ORDER BY 
  total_posts DESC
LIMIT 25