top_n_rank.sql

-- creating fake table for testing
CREATE TABLE products(product_id serial, 
  product_name text, 
  price numeric(11,2), 
  group_id integer
);

INSERT INTO products(product_name, price, group_id)
VALUES 
    ('Microsoft lumia', 200, 1),
    ('HTC One', 400, 1),
    ('Nexus', 500, 1),
    ('iPhone', 900, 1),
    ('HP Elite', 1200, 2),
    ('Lenovo Thinkpad', 700, 2),
    ('Sony VAIO', 700, 2),
    ('Dell Vostro', 800, 2),
    ('iPad', 700, 3),
    ('Kindle Fire', 150, 3),
    ('Samsung Galaxy Tab',200, 3);

-- median
SELECT group_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP(
           ORDER BY price
        ) as median
FROM products 
GROUP BY group_id;

-- avg
SELECT group_id,
       AVG(price)
FROM products
GROUP BY group_id;

-- avg n
SELECT x.group_id, avg(x.price)
FROM (
    SELECT product_name,
        group_id,
        price,
        RANK() OVER(
            PARTITION BY group_id
            ORDER BY price DESC
        ) as rank_order,
        ROW_NUMBER() OVER(
            PARTITION BY group_id
            ORDER BY price DESC
        ) as count_num
    FROM products
) as x
WHERE x.count_num <= 3
GROUP BY x.group_id;