Re: Adding AVG to a JOIN - Mailing list pgsql-general

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