article_count_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

int8

√

null

title

varchar

255

√

null

comments_total

int8

√

null

commenters_7d

int8

√

null

commenters_1d

int8

√

null

recent_comment_since

float8

17,17

√

null

score

float8

17,17

√

null

View Definition

SELECT comment_score.id,
    comment_score.title,
    comment_score.comments_total,
    comment_score.commenters_7d,
    comment_score.commenters_1d,
    comment_score.recent_comment_since,
    ((((((comment_score.comments_total * 1) / 5) + (comment_score.commenters_7d * 10)))::double precision + (((comment_score.commenters_1d * 50))::double precision *
        CASE
            WHEN (comment_score.recent_comment_since <= (8100)::double precision) THEN sqrt(((8100)::double precision / comment_score.recent_comment_since))
            ELSE (1)::double precision
        END)) * (
        CASE
            WHEN (comment_score.commenters_7d > 2) THEN 1
            ELSE 0
        END)::double precision) AS score
   FROM ( SELECT article.id,
            article.title,
            count(*) AS comments_total,
            count(DISTINCT
                CASE
                    WHEN ((now() - comment.created_at) <= '7 days'::interval) THEN comment.author_id
                    ELSE NULL::bigint
                END) AS commenters_7d,
            count(DISTINCT
                CASE
                    WHEN ((now() - comment.created_at) <= '1 day'::interval) THEN comment.author_id
                    ELSE NULL::bigint
                END) AS commenters_1d,
            date_part('epoch'::text, (now() - max(comment.created_at))) AS recent_comment_since
           FROM (article
             LEFT JOIN comment ON ((article.id = comment.article_id)))
          WHERE (comment.state = 'active'::text)
          GROUP BY article.id) comment_score;

Columns

View Definition

Possibly Referenced Tables/Views