thread album hist

threadalbumhist.txt

WITH thread_album_image_counts AS (
    SELECT 'all'::text AS the_set, forum_posts.thread_id, COUNT(*) AS count
    FROM relations
    JOIN forum_posts ON (
        forum_posts.id = relations.subject_id
    )
    JOIN forum_threads ON (
        forum_threads.id = forum_posts.thread_id
    )
    WHERE relations.type = 'cemi' AND
        relations.subject_type = get_huddler_constant('CONTENT_TYPE_POST')::int AND
        relations.object_type = get_huddler_constant('CONTENT_TYPE_GALLERY_IMAGE')::int
    GROUP BY forum_posts.thread_id
    ORDER BY COUNT(*) DESC
),
summary AS (
    SELECT AVG(count) AS avg, STDDEV(count) AS std
    FROM thread_album_image_counts
    GROUP BY the_set
)
SELECT avg, std, avg+2*std AS ninety_fith
FROM summary ;

huddler_headfi=> \i tmp/thread-album-hist.sql
        avg         |         std          |     ninety_fith      
--------------------+----------------------+----------------------
 8.8724035608308605 | 103.2105650033731624 | 215.2935335675771853


huddler_backyardchickens=> \i tmp/thread-album-hist.sql
        avg         |         std         |     ninety_fith      
--------------------+---------------------+----------------------
 7.1649351394788199 | 61.9937090475904316 | 131.1523532346596831
(1 row)