Wrong softcore users count in Achievement Distribution graph

[Mindhral]

Describe the bug
In the Achievement Distribution graph on a game's page, the bars give the hardcore players count in yellow and the softcore players count in gray for each possible number of achievements (or interval).
It works as long as there are only pure hardcore and pure softcore players, but the softcore user count is wrong if there is at least one player with unlocks in both modes.

To Reproduce
As this behavior is conditioned by changing data, it's possible my example becomes obsolete, but it shouldn't happen too soon.

1. Go to https://retroachievements.org/game/23766
2. At the time this ticket is made, the fastest way to see there is a problem is to look at the hidden table bellow the graph (CSS selector "#chart_distribution table")
3. The data for 16 achievements shows 1 hardcore player and -1 softcore player, which is obviously wrong.

Expected behavior
Either real softcore players counts, or (better in my opinion) switching the second bar to "Total players" (or a better wording).

Screenshots

Additional context
The data returned by the DB query is the number of hardcore players and the total number of players for each number of achievements. The code in

RAWeb/app/Helpers/render/game.php

Line 354 in b0ce383

function calculateBuckets(

calculates the number of softcore players as the subtraction of the 2 numbers.

But a player who has mixed unlocks doesn't necessarily count in the same bucket for hardcore and total, so you can't remove them from one to get a complement of the other (I know it's not very clear, sorry).
Generally speaking, we can't determine the number of softcore players with the data returned by the DB query (I can give a simple example with 3 players and 2 achievements where there can be 2 possible situations leading to the same query result).

Event if the DB result was changed, hardcore players and softcore players should not be drawn the way they are in my opinion, as their addition doesn't mean anything when there are players with mixed unlocks (imagine a player with 2 unlocks in hardcore and 2 unlocks in softcore). Which is why I suggest to change the label and number shown, to reflect exactly what is returned by the query: hardcore and total.

[TwosomesUP]

This seems to be an issue with how the achievement distribution query is pulling the counts for unique Softcore vs. unique Hardcore unlocks. Like mentioned above, when pulling Softcore counts, the current query pulls "total unlocks" instead of actual softcore unlocks. Just a POC, but upon initial testing slightly tweaking the SQL, like the example below, should fix it:

if ($hardcore){
	$query = "SELECT a.achievements_unlocked_hardcore AS AwardedCount, 
				COUNT(CASE WHEN a.achievements_unlocked_hardcore > 0 THEN 1 ELSE 0 END) AS NumUniquePlayers
				FROM player_games a
				WHERE a.game_id = :gameId
				AND a.achievements_unlocked_hardcore > 0
				GROUP BY a.achievements_unlocked_hardcore
				ORDER BY a.achievements_unlocked_hardcore DESC";
} else {
	$query = "SELECT a.achievements_unlocked - a.achievements_unlocked_hardcore AS AwardedCount, 
				COUNT(CASE WHEN a.achievements_unlocked - a.achievements_unlocked_hardcore > 0 THEN 1 ELSE 0 END) AS NumUniquePlayers
				FROM player_games a
				WHERE a.game_id = :gameId
				GROUP BY a.achievements_unlocked - a.achievements_unlocked_hardcore
				ORDER BY a.achievements_unlocked - a.achievements_unlocked_hardcore DESC";
}

Old behavior:

New behavior:

[Mindhral]

Is it normal that you have 3 players with softcore unlocks, but only a sum of 2 in the gray bars?

Also, are you sure you want to stack a hardcore count and a softcore count, when there addition doesn't really mean anything? And that you want to forget that one players has 4 achievements total (2 in hardcore, 2 in softcore)?

A solution might be to consider hardcore players (same as today) and total players, but not stacked:

The downside is it is a bit cramped.

[TwosomesUP]

You're right, there was a bit of a discrepancy that I was able to fix. Here's how it's currently displayed:

In this case we have 5 players. 1 with 2 softcore/2 hardcore, 1 with 2 softcore, 1 with 3 softcore, and 2 with 4 hardcore. The player with a divide of hardcore and softcore unlocks is represented in the first bar (as well as the player with 2 softcore). It reads:

Earned 2 Hardcore Achievements (1 player)
Earned 2 Softcore Achievements (2 players)

At this point, I don't believe the "unstacked" bar graph is the direction RA wants to go, but hopefully this change will help give a better representation of the hardcore vs. softcore distribution.

[Mindhral]

All right, although I find weird to have the player with 2 softcore / 2 hardcore being "added" with himself in the 2nd column.
That also means that you can have a player who completed a set (with mixed mode) and nothing in the last column, which is what I would spontaneously look to have the information.

It turns out to be harder to represent that data that I would have thought intuitively...

[TwosomesUP]

Yeah, it's a tough one. The way I interpret this is distribution of softcore vs. hardcore unlock counts in specific ranges, not necessarily total unlocks for a player.

If a player unlocked all achievements for a game that had 10 achievements, but completed 8 in hardcore and 2 in softcore, they would be represented in the 8 unlocks column (hardcore) and 2 unlocks column (softcore).