Re: Adding AVG to a JOIN

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you, Daniel -

On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite <daniel@xxxxxxxxxxxxxxxx> 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