I have a table User that only has an id and a name. A user has many Games, which is a table that contains an id, a score, inserted_at and a user_id referencing the User table. I would like that my query returns the following result:
| label | user_id | data |
|---|---|---|
| Jacques | 1 | {null, 1, 4, 3, null, 6, null, 8, 9, null, 2, 5} |
| Marine | 2 | {null, null, null, 4, 6, 2, 8, 6, 10, 10, 3, 8} |
where label is the name of the user and data is the average score of each games, per user, per unit of time within a time interval (here it is per month for the last 12 months) from this query:
SELECT DATE_TRUNC('month', generate_series(LOCALTIMESTAMP - INTERVAL '11 month', LOCALTIMESTAMP, '1 month')) as time_unit)
which return this result:
"2021-07-01 00:00:00"
"2021-08-01 00:00:00"
"2021-09-01 00:00:00"
"2021-10-01 00:00:00"
"2021-11-01 00:00:00"
"2021-12-01 00:00:00"
"2022-01-01 00:00:00"
"2022-02-01 00:00:00"
"2022-03-01 00:00:00"
"2022-04-01 00:00:00"
"2022-05-01 00:00:00"
"2022-06-01 00:00:00"
The unit of time can be changed, in the above query, it is every months from the last 12 months, but it could be every months from the last 6 months, or every day for the last 2 weeks.
The NULL are important, because this data is then to be fed to Chart.js, so I need to know when a user hasn't played a game and scored anything.
So far I have two attempts:
Attempt 1:
(SELECT time_unit, avg, u.name as user_name, u.id as user_id
FROM users u
CROSS JOIN (
SELECT time_unit, AVG(ROUND(g.score, 1))
FROM (SELECT DATE_TRUNC('month', generate_series(LOCALTIMESTAMP - INTERVAL '3 year', LOCALTIMESTAMP, '1 month')) as time_unit) as time_unit_query
LEFT JOIN games g
ON DATE_TRUNC('month', g.inserted_at) = time_unit
LEFT JOIN users u
ON u.id = g.user_id
GROUP BY time_unit, u.name, u.id
) as q1
ORDER By 1);
Which returns the roughly this result:
time_unit | score | user_name | user_id
...
"2021-05-01 00:00:00", null, "Marine", 1
"2021-05-01 00:00:00", null, "Jacques", 2
"2021-05-01 00:00:00", null, "Jean", 3
"2021-06-01 00:00:00", 6.0000000000000000, "Marine", 1
"2021-06-01 00:00:00", 6.0000000000000000, "Jacques", 2
"2021-06-01 00:00:00", 6.0000000000000000, "Jean", 3
"2021-06-01 00:00:00", 7.8333333333333333, "Marine", 1
"2021-06-01 00:00:00", 7.8333333333333333, "Jacques", 2
"2021-06-01 00:00:00", 7.8333333333333333, "Jean", 3
...
As you can see, the results are wrong because all users have the same score, which is not the case in the database.
Attempt 2:
SELECT u.name AS label,
u.id,
ARRAY(
SELECT AVG(ROUND(g.score))
FROM (SELECT DATE_TRUNC('month', generate_series(LOCALTIMESTAMP - INTERVAL '1 year', LOCALTIMESTAMP, '1 month')) as time_unit) as time_unit_query
LEFT JOIN games g
ON DATE_TRUNC('month', g.inserted_at) = time_unit
GROUP BY time_unit, user_id
)
FROM games g
JOIN users u
ON u.id = g.user_id
GROUP BY u.id
returns this:
user_name | user_id | data
"Jean" 3 {6,5.222222222222222,2.625,7.833333333333333,4.6,5.111111111111111,NULL,6,6.5,6.888888888888889,6.545454545454546,4.125,5.666666666666667,6.666666666666667,4,7,5,5.75,4.428571428571429,5.545454545454546,5.111111111111111,5.25,4.375,6.181818181818182,5.083333333333333,6,5.666666666666667,5.857142857142857,5.8,6.625,6.875,5.125}
"Jacques" 2 {6,5.222222222222222,2.625,7.833333333333333,4.6,5.111111111111111,NULL,6,6.5,6.888888888888889,6.545454545454546,4.125,5.666666666666667,6.666666666666667,4,7,5,5.75,4.428571428571429,5.545454545454546,5.111111111111111,5.25,4.375,6.181818181818182,5.083333333333333,6,5.666666666666667,5.857142857142857,5.8,6.625,6.875,5.125}
"Marine" 1 {6,5.222222222222222,2.625,7.833333333333333,4.6,5.111111111111111,NULL,6,6.5,6.888888888888889,6.545454545454546,4.125,5.666666666666667,6.666666666666667,4,7,5,5.75,4.428571428571429,5.545454545454546,5.111111111111111,5.25,4.375,6.181818181818182,5.083333333333333,6,5.666666666666667,5.857142857142857,5.8,6.625,6.875,5.125}
As you can see here too, all users have the same score ...
Attempt 3:
WITH span AS (
SELECT
1 AS user_id,
NULL AS avg,
DATE(series) AS date
FROM GENERATE_SERIES(DATE_TRUNC('month', NOW() - INTERVAL '11' MONTH), NOW(), '1 month') AS series
UNION
SELECT
games.user_id, ROUND(AVG(games.score), 1) AS avg, DATE_TRUNC('month', games.inserted_at) AS date
FROM games
WHERE games.user_id = 1
AND games.inserted_at > NOW()-INTERVAL '11' MONTH
GROUP BY games.user_id, date
ORDER BY date)
SELECT
users.name AS name,
ARRAY_AGG(s.avg) AS avg
FROM
(SELECT user_id, ROUND(AVG(avg), 1) AS avg, date FROM span GROUP BY user_id, date ORDER BY date) AS s JOIN users ON s.user_id = users.id
GROUP BY
name;
returns this:
user | data
"Marine" {6.8,6.6,4.4,5.3,7.0,5.7,5.8,6.0,5.0,4.0,6.5,NULL}
but only works for one user.
How can I get the correct score for each user and how can I aggregate the result into an array so that I get the data in the proper format?
