How can I display top 3 products with its price and arrange by average prices?

Question

I have this records in products table with product_id and its price

product_id	price
1	150
1	190
2	20
2	12
3	123
4	513
5	157
5	147

and I want to get the top 3 products and arrange it by average price something like this

product_id	price	avg_price
4	513	513
1	150	170
1	190	170
5	157	152
5	147	152

how to write/code it in sql query or laravel eloquent query?

Answer 1

WITH AverageCTE AS
(
    SELECT product_id, AVG(avg_price) as avg_price 
    FROM products
    GROUP BY product_id
)

SELECT p.product_id, price, avg_price
FROM product p JOIN 
(SELECT * FROM AverageCTE ORDER BY avg_price DESC LIMIT 3) a 
    on p.product_id = a.product_id
ORDER BY avg_price DESC