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);
https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/2
Regards
Alex