Error: SELECT list expression references column created_utc which is neither grouped nor aggregated at [2:49]

Question

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

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  EXTRACT(WEEK FROM TIMESTAMP_SECONDS(created_utc)) AS week_number,
  ARRAY_AGG(id ORDER BY score DESC LIMIT 1)[OFFSET(0)] id,
  ARRAY_AGG(score ORDER BY score DESC LIMIT 1)[OFFSET(0)] highest_score    
FROM `project.dataset.table` 
WHERE body = 'r/twinpeaks'
GROUP BY week_number
ORDER BY week_number