Re: Adding AVG to a JOIN

Thank you, Daniel -

On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite <daniel(at)manitou-mail(dot)org>
wrote:

>
> You may use a correlated subquery in the SELECT clause,
> like this:
>
> SELECT
> u.elo,
> u.uid,
> (SELECT AVG(score) FROM words_moves WHERE uid=u.uid),
> s.given,
> s.photo
>

this has worked great for me:

SELECT
u.elo,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE
uid = u.uid) AS 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

Is that what is called LATERAL JOIN?

Regards
Alex