glints.public

Parameters

Name	Type	Mode
application_count	bigint	TABLE
job_count	bigint	TABLE
user_count	bigint	TABLE
company_count	bigint	TABLE
candidate_count	bigint	TABLE
active_users	bigint	TABLE
monday	timestamp with time zone	TABLE

Definition

BEGIN
  RETURN QUERY
  WITH application_count AS
  (SELECT COUNT(*) as "Application Count",
          date_trunc('week', "createdAt") as week
   FROM "Applications"
   GROUP BY week
   ORDER BY week),
      job_count AS
    (SELECT COUNT(*) as "Job Count",
            date_trunc('week', "createdAt") as week
     FROM "Jobs"
     GROUP BY week
     ORDER BY week),
      user_count AS
    (SELECT COUNT(*) as "User Count",
            date_trunc('week', "createdAt") as week
     FROM "Users"
     GROUP BY week
     ORDER BY week),
      company_count AS
    (SELECT COUNT(*) as "Company Count",
            date_trunc('week', "createdAt") as week
     FROM "Users"
     WHERE preferences ->> 'profileMode' = 'company'
     GROUP BY week
     ORDER BY week),
      candidate_count AS
    (SELECT COUNT(*) as "Candidate Count",
            date_trunc('week', "createdAt") as week
     FROM "Users"
     WHERE preferences ->> 'profileMode' = 'candidate'
     GROUP BY week
     ORDER BY week),
      active_users AS
    (SELECT count(*) as "Active User Count",
       week
     FROM (SELECT "userId", date_trunc('week', "createdAt") as week
           FROM "ActionLogs"
           GROUP BY "userId", week
           ORDER BY week)
       AS "UNIQUE"
     GROUP BY week)
  SELECT "Application Count", "Job Count", "User Count", "Company Count", "Candidate Count", "Active User Count", company_count.week as "Monday of Week"
  FROM application_count, job_count, user_count, company_count, candidate_count, active_users
  WHERE application_count.week = user_count.week
        AND user_count.week = company_count.week
        AND company_count.week = candidate_count.week
        AND candidate_count.week = job_count.week
        AND job_count.week = active_users.week;
END

weekly()

Parameters

Definition