CREATE OR REPLACE VIEW forum_thread_meta AS
WITH
  all_forum_posts_extra AS (
    SELECT forum_thread_id,
        LAST_VALUE(forum_post_id) OVER w AS last_post_id,
        LAST_VALUE(ctime) OVER w AS last_post_ctime,
        COUNT(forum_post_id) OVER w as post_count,
        ROW_NUMBER() OVER w AS rn
    FROM forum_posts
      WINDOW w AS (PARTITION BY forum_thread_id ORDER BY ctime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ),
  forum_posts_extra AS (
    SELECT *
    FROM all_forum_posts_extra
    WHERE rn = 1
  )
SELECT forum_thread_id, key, ctime, title, title_mtime, forum_section_id, is_pinned, is_locked, COALESCE(post_count, 0) AS post_count, last_post_ctime
FROM forum_threads
  LEFT OUTER JOIN forum_posts_extra USING (forum_thread_id);