VUserDailyPoints

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

UserId

uuid

2147483647

√

null

user id, can reference to public.Users

date

timestamptz

35,6

√

null

date

qnCount

int4

10

√

null

how many question this user created at the date

ansCount

int4

10

√

null

how many answer this user created at the date

qnUpvoteCount

int4

10

√

null

how many upvotes this user received from their questions at the date

ansUpvoteCount

int4

10

√

null

how many upvotes this user received from their answers at the date

dailyPoints

int4

10

√

null

how many points user earned at the date(maximal is 200)

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))
        )
 SELECT data_report.user_id AS "UserId",
    data_report.date,
    (data_report.qn_count)::integer AS "qnCount",
    (data_report.ans_count)::integer AS "ansCount",
    (data_report.qn_upvote_count)::integer AS "qnUpvoteCount",
    (data_report.ans_upvote_count)::integer AS "ansUpvoteCount",
    (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))::integer AS "dailyPoints"
   FROM data_report;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views