Streaks with post count

WITH raw_posts AS (
    SELECT created::date AS post_date
    FROM blog_entry
    UNION ALL
    SELECT created::date
    FROM blog_blogmark
    UNION ALL
    SELECT created::date
    FROM blog_quotation
),
dates_and_counts AS (
    SELECT 
        post_date,
        COUNT(*) as posts_this_date
    FROM raw_posts
    GROUP BY post_date
),
streak_groups AS (
    SELECT 
        post_date,
        posts_this_date,
        COUNT(*) OVER (PARTITION BY dateadd) as streak_length,
        FIRST_VALUE(post_date) OVER (PARTITION BY dateadd ORDER BY post_date) as start_date,
        LAST_VALUE(post_date) OVER (
            PARTITION BY dateadd 
            ORDER BY post_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) as end_date,
        SUM(posts_this_date) OVER (PARTITION BY dateadd) as total_posts,
        ROW_NUMBER() OVER (PARTITION BY dateadd) as rn
    FROM (
        SELECT 
            post_date,
            posts_this_date,
            post_date - (ROW_NUMBER() OVER (ORDER BY post_date))::integer * interval '1 day' as dateadd
        FROM dates_and_counts
    ) d
)
SELECT 
    streak_length as duration_in_days,
    start_date,
    end_date,
    total_posts as num_posts
FROM streak_groups
WHERE rn = 1
ORDER BY start_date DESC