On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber <alexander.farber@xxxxxxxxx> wrote: > > Last question please - how to run the query for all users? > > I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL way? > > How to refer to the outside "uid" from inside the CTE in the query below? > > WITH diffs AS ( > SELECT > gid, > uid, > played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff > FROM moves > WHERE uid = how_to_refer_to_the_outside_uid > ) > UPDATE users SET avg_time = > ( > SELECT > AVG(diff) > FROM diffs > GROUP BY uid > ) > WHERE uid IN (SELECT uid FROM users); UPDATE users SET avg_time = diffs.average_time_for_the_grouped_by_user FROM diffs WHERE users.uid = diffs.uid --< the missing "where" I commented about earlier But you need to construct the "diffs" CTE/subquery so that it Group[s] By uid David J.
