|
| 1 | +# Time: O(n^2 * logn) |
| 2 | +# Space: O(n^2) |
| 3 | + |
| 4 | +WITH stores_cteAS ( |
| 5 | +SELECTs.store_id, |
| 6 | +s.store_name, |
| 7 | +s.location, |
| 8 | +MAX(i.price)AS max_price, |
| 9 | +MIN(i.price)AS min_price |
| 10 | +FROM stores s |
| 11 | +INNER JOIN inventory iONs.store_id=i.store_id |
| 12 | +GROUP BY1,3 |
| 13 | +HAVINGCOUNT(DISTINCTi.product_name)>=3 |
| 14 | +ORDER BYNULL |
| 15 | +), |
| 16 | +products_cteAS ( |
| 17 | +SELECTi.store_id, |
| 18 | +s.store_name, |
| 19 | +s.location, |
| 20 | +i.quantity, |
| 21 | +i.product_name, |
| 22 | +i.price |
| 23 | +FROM inventory i |
| 24 | +INNER JOIN stores_cte sONi.store_id=s.store_idAND (i.price=s.max_priceORi.price=s.min_price) |
| 25 | +) |
| 26 | + |
| 27 | +SELECTp1.store_id, |
| 28 | +p1.store_name, |
| 29 | +p1.location, |
| 30 | +p1.product_nameAS most_exp_product, |
| 31 | +p2.product_nameAS cheapest_product, |
| 32 | + ROUND(p2.quantity/p1.quantity,2)AS imbalance_ratio |
| 33 | +FROM products_cte p1 |
| 34 | +INNER JOIN products_cte p2ONp1.store_id=p2.store_idANDp1.price>p2.priceANDp1.quantity<p2.quantity |
| 35 | +ORDER BY6DESC,2; |