Multiple aggregate operations via a pivot table

Question

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;

Answer 1

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;