Re: Adding AVG to a JOIN

Thank you for the explanation. I have rearranged my query and it works now
(surprisingly fast too) -

On Mon, Apr 23, 2018 at 9:58 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber <
> alexander(dot)farber(at)gmail(dot)com> wrote:
>
>> SELECT
>> u.elo,
>> 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;

Best regards
Alex