PostgreSQL – Performing Multiple Aggregate Operations via Pivot Table

postgresql

My goal is to obtain for each user a total lifetime achievement points as well as points obtained in ranges, i.e. for the last three months.

I have this working but I'm curious if my solution will scale or if theirs a better way of thinking about the problem, in particular, I'm using Postgres so particular features in that DB might be a good solution for this.

Table Structure

Users:
------
id: int

AchievementsUser
----------------
id: int
user_id: int
achievement_id: int
inserted_at: DateTime

Achievement
----------------
id: int
value: int

Solution description

  • Left join on the pivot table to get the achievements per user. This is built as a CTE (base in the SQL)
  • Produce a further CTE for each date range I'm interested in. These provide the filters, and sum operations (i.e. later, I could do averages if I wanted)

SQL

WITH base AS (
  SELECT 
    users.id AS user_id, 
    achievements.value AS value, 
    achievements_users.inserted_at AS inserted_at 
  FROM 
    users, 
    achievements_users, 
    achievements 
  WHERE 
    users.id = achievements_users.user_id 
    AND achievements.id = achievements_users.achievement_id
), 
all_time AS (
  SELECT 
    base.user_id, 
    SUM(base.value) 
  FROM 
    base 
  GROUP BY 
    base.user_id
), 
last_three_months AS (
  SELECT 
    base.user_id, 
    SUM(base.value) 
  FROM 
    base 
  WHERE 
    base.inserted_at > CURRENT_DATE - INTERVAL '3 months' 
  GROUP BY 
    base.user_id
) 
SELECT 
  all_time.user_id, 
  all_time.sum AS all_time, 
  last_three_months.sum AS last_three_months 
FROM 
  all_time, 
  last_three_months 
WHERE 
  all_time.user_id = last_three_months.user_id;

Best Answer

Use conditional aggregation:

SELECT u.id AS user_id, 
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at >= current_date - interval '3 months') as last_three_months 
FROM users u
  JOIN achievements_users au ON u.id = au.user_id 
  JOIN achievements a ON a.id = au.achievement_id
GROUP BY u.id;

You don't really need the users table though:

SELECT au.user_id, 
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at >= current_date - interval '3 months') as last_three_months 
FROM achievements_users au
  JOIN achievements a ON a.id = au.achievement_id
GROUP BY u.id;
Related Question