AVG(c.played - c.prev_played) AS avg_time_per_move,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS score,
And I don't understand why adding a CTE has caused it, because without the CTE the GROUP BY u.elo was not required...
Adding "AVG(c.played - c.prev_played)" directly to the top-level select statement column list is what turned it into a "GROUP BY" query. When you embedded the "AVG(score)" in a subquery the GROUP BY was limited to just that subquery, and it had no other columns besides the aggregate and so didn't require a GROUP BY clause.