Complex point aggregation with modifiers

I have the following tables in a Postgres 16.5 database, tracking player completions for levels, with some modifiers:

CREATE TABLE levels (
  id int PRIMARY KEY
, points int NOT NULL
);

CREATE TABLE completions(
  user_id int
, level_id int REFERENCES levels
, full_completion boolean
, alternate_strategy boolean
, is_fastest boolean
);

INSERT INTO levels VALUES
  (1, 100)
, (2,  50)
, (3,  10)
;

INSERT INTO completions VALUES
  (1, 1, true , false, false)
, (1, 1, false, true , false)  --> 500
, (1, 2, true , true , false)
, (1, 2, true , false, false)  --> 300
, (1, 3, false, true , true )
, (1, 3, true , false, true )
, (1, 3, false, false, false)  -->  70
;

Every row in completions is unique, so users can have multiple completions in the same level, but with different modifiers.

Levels can be worth as little as one point, but it's always a natural number (so strictly greater than zero).

The points for the modifiers are assigned as follows:

• If full_completion is true, points are worth triple
• If alternate_strategy is true, points are worth double (stacks multiplicatively)
• If is_fastest is true, sum 20 to points. This after the multiplications.
• If no modifiers, simply add the points as they are, but only if there's no other completions that are worth more.

Here are some examples of the completions table, assuming a level 1 worth 100 points:

1, 1, true, false, false
1, 1, false, true, false

Here, the first row would give 100*3 points, and the second 100*2, for a total of 500 points

1, 1, true, true, false
1, 1, true, false, false

Here, the first row would give us 100*3*2 points, while the second row would give us 0 points because we have already used the first modifier in the first completion. The total is 600 points.

1, 1, false, true, true
1, 1, true, false, true
1, 1, false, false, false

Here, the second row would give us 100*3+20 points, while the first only 100*2 but not the +20, because it was already used once. The third row nets no points. The total is 520.

I need a query that outputs player ID, level ID, and points gained for that level, accounting for all completions of the player in that level - ideally in a single query to calculate a leaderboard.

As for the completion modifier priority rules, I'll describe them some more:

• The completion where the multiplier has to be accounted in is the one with the highest multiplier, not the highest final score.
• is_fastest being true in any of the completions should have the effect of adding 20 to the final score, regardless of how many completions have it. If we have 2 runs: (t, t, t), (f, f, t), the first row would give points*3*2+20, while the second would give 0, because the +20 has already been added, and it wouldn't just add points to the user again because that's already accounted for in the first row.

Simpler query for your updated rules:

SELECT c.user_id, c.level_id, l.points * mult + add AS total_score
FROM  (
   SELECT user_id, level_id
        , CASE WHEN bool_or(full_completion AND alternate_strategy) THEN 6
               ELSE GREATEST (CASE WHEN bool_or(full_completion)    THEN 3 ELSE 0 END
                            + CASE WHEN bool_or(alternate_strategy) THEN 2 ELSE 0 END, 1)
               END AS mult
        , CASE WHEN bool_or(is_fastest) THEN 20 ELSE 0 END AS add
   FROM   completions
   GROUP  BY 1, 2
   ) c
JOIN   levels l ON l.id = c.level_id
ORDER  BY 1, 2;

fiddle

Produces your desired result in a single query.

In subquery c, compute multiplier mult and addition add with the aggregate function bool_or(). In the outer SELECT do the math.