Multiple aggregate operations via a pivot table
Use conditional aggregation:
SELECT u.id AS user_id, 
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at &gt;= 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 &gt;= 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;