I have a table: t
My goal: To extract only the "id" with the highest score in the table and group it by the week_number.
Query:
SELECT id,
CAST(EXTRACT(WEEK FROM TIMESTAMP_SECONDS(created_utc)) AS STRING) AS week_number,
MAX(score) AS highest_score
FROM t
WHERE body='r/twinpeaks'
GROUP BY id;
I am getting this error:
Error: SELECT list expression references column created_utc which is neither grouped nor aggregated at [2:49]
I tried doing this instead:
SELECT id,
CAST(EXTRACT(WEEK FROM TIMESTAMP_SECONDS(created_utc)) AS STRING) AS week_number,
MAX(score) AS highest_score
FROM t
WHERE body='r/twinpeaks'
GROUP BY week_number, id;
But this is what I got:
Row id week_number highest_score
1 dmkb6sv 36 1
2 dn1cd2s 37 2
3 dn43h1k 38 16
4 dn3xf18 38 1
5 dn7i1ko 38 1
6 dnpr9b1 39 1
I want this:
Row id week_number highest_score
1 dmkb6sv 36 1
2 dn1cd2s 37 2
3 dn43h1k 38 16
6 dnpr9b1 39 1
Best Answer
Below is for BigQuery Standard SQL