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?
>Solution :
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