Window functions partition and order without subquery

Question

Given a simple table like so in postgres:

CREATE TABLE products (
   product_id serial PRIMARY KEY,
   group_id INT NOT NULL,
   price DECIMAL (11, 2)
);

INSERT INTO products (product_id, group_id,price)
VALUES
   (1, 1, 200),
   (2, 1, 400),
   (3, 1, 500),
   (4, 1, 900),
   (5, 2, 1200),
   (6, 2, 700),
   (7, 2, 700),
   (8, 2, 800),
   (9, 3, 700),
   (10, 3, 150),
   (11, 3, 200);

How do I query using window functions the group_id and the avg_price, order by avg_price? So the current result I have is only via a subquery:

select * from (
  select 
    distinct group_id, 
    avg(price) over (partition by group_id) avg_price 
  from products) 
a order by avg_price desc; 

But I believe there are more elegent solutions to this.

Answer 1

Window functions can be used in the ORDER BY clause, in addition to the SELECT clause, so the following query is valid:

SELECT
    group_id,
    AVG(price) OVER (PARTITION BY group_id) avg_price
FROM products
ORDER BY
    AVG(price) OVER (PARTITION BY group_id);

However, given that you seem to want to use DISTINCT, I suspect that what you really want here is a GROUP BY query:

SELECT
    group_id,
    AVG(price) AS avg_price
FROM products
GROUP BY
    group_id
ORDER BY
    AVG(price);