Thank you, the following seems to have worked -
On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
>
> 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
>
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
),
avg_diffs AS (
SELECT uid, AVG(diff) as avg_diff FROM diffs GROUP BY uid
)
UPDATE users SET avg_time = avg_diff
FROM avg_diffs
WHERE users.uid = avg_diffs.uid;
https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/9Or did you mean something else?
Regards
Alex