VUserTotalPoints

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

UserId

uuid

2147483647

√

null

user id, can reference to public.Users

totalPoints

int4

10

√

null

total points this user earned over time

Relationships

Close relationships within degrees of separation

One

View Definition

WITH qn_count AS (
         SELECT date_trunc('d'::text, "Questions"."createdAt") AS date,
            "Questions"."UserId" AS user_id,
            count(*) AS qn_count
           FROM qn_ans."Questions"
          WHERE ((1 = 1) AND ("Questions"."deletedAt" IS NULL))
          GROUP BY (date_trunc('d'::text, "Questions"."createdAt")), "Questions"."UserId"
        ), ans_count AS (
         SELECT date_trunc('d'::text, "Answers"."createdAt") AS date,
            "Answers"."UserId" AS user_id,
            count(*) AS ans_count
           FROM qn_ans."Answers"
          WHERE ((1 = 1) AND ("Answers"."deletedAt" IS NULL))
          GROUP BY (date_trunc('d'::text, "Answers"."createdAt")), "Answers"."UserId"
        ), qn_upvote_count AS (
         SELECT date_trunc('d'::text, qu."createdAt") AS date,
            qn."UserId" AS user_id,
            count(qu.id) AS qn_upvote_count
           FROM (qn_ans."QuestionUpvotes" qu
             LEFT JOIN qn_ans."Questions" qn ON ((qn.id = qu."QuestionId")))
          WHERE ((1 = 1) AND (qu."deletedAt" IS NULL) AND (qn."deletedAt" IS NULL))
          GROUP BY (date_trunc('d'::text, qu."createdAt")), qn."UserId"
        ), ans_upvote_count AS (
         SELECT date_trunc('d'::text, au."createdAt") AS date,
            ans."UserId" AS user_id,
            count(au.id) AS ans_upvote_count
           FROM (qn_ans."AnswerUpvotes" au
             LEFT JOIN qn_ans."Answers" ans ON ((ans.id = au."AnswerId")))
          WHERE ((1 = 1) AND (au."deletedAt" IS NULL) AND (ans."deletedAt" IS NULL))
          GROUP BY (date_trunc('d'::text, au."createdAt")), ans."UserId"
        ), data_report AS (
         SELECT user_id,
            date,
            COALESCE(qn_count.qn_count, (0)::bigint) AS qn_count,
            COALESCE(ans_count.ans_count, (0)::bigint) AS ans_count,
            COALESCE(qn_upvote_count.qn_upvote_count, (0)::bigint) AS qn_upvote_count,
            COALESCE(ans_upvote_count.ans_upvote_count, (0)::bigint) AS ans_upvote_count
           FROM (((qn_count
             FULL JOIN ans_count USING (user_id, date))
             FULL JOIN qn_upvote_count USING (user_id, date))
             FULL JOIN ans_upvote_count USING (user_id, date))
        ), daily_points AS (
         SELECT data_report.user_id AS "UserId",
            data_report.date,
            LEAST(((((data_report.qn_count * 15) + (data_report.ans_count * 10)) + (data_report.qn_upvote_count * 5)) + (data_report.ans_upvote_count * 5)), (200)::bigint) AS points
           FROM data_report
        )
 SELECT daily_points."UserId",
    (sum(daily_points.points))::integer AS "totalPoints"
   FROM daily_points
  GROUP BY daily_points."UserId";

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views