article_activity_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

int8

√

null

title

varchar

255

√

null

created_at

timestamptz

35,6

√

null

base_score

numeric

131089

√

null

boost_score_1

int8

√

null

boost_score_2

float8

17,17

√

null

score

float8

17,17

√

null

View Definition

SELECT scores.id,
    scores.title,
    scores.created_at,
    scores.base_score,
    scores.boost_score_1,
    scores.boost_score_2,
    (((scores.base_score + (scores.boost_score_1)::numeric))::double precision + scores.boost_score_2) AS score
   FROM ( SELECT article.id,
            article.title,
            article.created_at,
            LEAST(((((0.5 * (COALESCE(cc.comment_12_hrs, (0)::bigint))::numeric) + ((2 * COALESCE(lc1.like_24_hrs, (0)::bigint)))::numeric) * ((2 + pd.post_days))::numeric) / ((1 + pd.post_days))::numeric), (300)::numeric) AS base_score,
            ((15 * COALESCE(cc.comment_30_mins, (0)::bigint)) + (60 * COALESCE(lc2.like_30_mins, (0)::bigint))) AS boost_score_1,
            (GREATEST(((120)::double precision - ((2)::double precision * fc.since_comment)), (0)::double precision) + GREATEST(((240)::double precision - ((2)::double precision * fl.since_like)), (0)::double precision)) AS boost_score_2
           FROM ((((((article
             LEFT JOIN ( SELECT comment.article_id,
                    sum(((comment.created_at >= (now() - '12:00:00'::interval)))::integer) AS comment_12_hrs,
                    sum(((comment.created_at >= (now() - '00:30:00'::interval)))::integer) AS comment_30_mins
                   FROM comment
                  GROUP BY comment.article_id) cc ON ((cc.article_id = article.id)))
             LEFT JOIN ( SELECT appreciation.reference_id,
                    sum(appreciation.amount) AS like_24_hrs
                   FROM appreciation
                  WHERE ((appreciation.purpose = 'appreciate'::text) AND (appreciation.created_at >= (now() - '24:00:00'::interval)))
                  GROUP BY appreciation.reference_id) lc1 ON ((lc1.reference_id = article.id)))
             LEFT JOIN ( SELECT appreciation.reference_id,
                    sum(appreciation.amount) AS like_30_mins
                   FROM appreciation
                  WHERE ((appreciation.purpose = 'appreciate'::text) AND (appreciation.created_at >= (now() - '00:30:00'::interval)))
                  GROUP BY appreciation.reference_id) lc2 ON ((lc2.reference_id = article.id)))
             LEFT JOIN ( SELECT article_1.id,
                    (CURRENT_DATE - (article_1.created_at)::date) AS post_days
                   FROM article article_1) pd ON ((pd.id = article.id)))
             LEFT JOIN ( SELECT comment.article_id,
                    (date_part('epoch'::text, (now() - min(comment.created_at))) / (60)::double precision) AS since_comment
                   FROM comment
                  WHERE (comment.created_at >= (now() - '30 days'::interval))
                  GROUP BY comment.article_id) fc ON ((fc.article_id = article.id)))
             LEFT JOIN ( SELECT appreciation.reference_id,
                    (date_part('epoch'::text, (now() - min(appreciation.created_at))) / (60)::double precision) AS since_like
                   FROM appreciation
                  WHERE ((appreciation.purpose = 'appreciate'::text) AND (appreciation.created_at >= (now() - '30 days'::interval)))
                  GROUP BY appreciation.reference_id) fl ON ((fl.reference_id = article.id)))) scores
  WHERE (NOT (scores.id IN ( SELECT matters_today.article_id
           FROM matters_today
          ORDER BY matters_today.updated_at DESC
         LIMIT 1)));

Columns

View Definition

Possibly Referenced Tables/Views