BigQuery SQL, Obtain Median, Grouped by Date?

Question

When trying to obtain, say the median using the partition by window function, I receive an error message "SELECT list expression references column seller_stock which is neither grouped nor aggregated", why is this, how must i write this SQL differently? I have many records per day, and i want to return the median for each day ...

SELECT date(snapshot_date) AS period, 
  PERCENTILE_DISC(**seller_stock**, 0.5) OVER (PARTITION BY snapshot_date) AS median_stock
FROM `table.name`  
WHERE snapshot_date >= "2022-04-01" 
GROUP BY snapshot_date

Answer 1

The thing is that you cannot group by an AGG function, since you are getting already the median there over by your rows, you will need just the top row of that statement.

You can use an intermediate table or aux.

This is an example:

with median_data as (
  select 
    date(snapshot_date) AS period,
    PERCENTILE_DISC(seller_stock, 0.5) OVER (PARTITION BY snapshot_date) AS median_stock,
    row_number() over(order by snapshot_date) as r
  from `table.name`
  where snapshot_date >= "2022-04-01" 
)

select period,median_stock from median_data where r =  1