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.
WITH cte AS ( SELECT m.gid, m.uid, m.played, LAG(m.played) OVER (PARTITION BY m.gid ORDER BY played) AS prev_played FROM words_moves m JOIN words_games g ON (m.gid = g.gid AND m.uid in (g.player1, g.player2)) WHERE m.played > CURRENT_TIMESTAMP - interval '1 month' ) SELECT u.elo, (SELECT TO_CHAR(AVG(played - prev_played), 'HH24:MI') FROM cte WHERE uid = u.uid) AS avg_time, (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score, s.given, s.photo FROM words_users u JOIN words_social s USING (uid) WHERE u.elo > 1500 -- take the most recent record from words_social AND NOT EXISTS (SELECT 1 FROM words_social x WHERE s.uid = x.uid AND x.stamp > s.stamp) -- only show players who where active in the last week AND EXISTS (SELECT 1 FROM words_moves WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week' AND action IN ('play', 'skip', 'swap', 'resign')) ORDER BY u.elo DESC LIMIT 10;
