Provide the item with the second highest and second lowest sales. Also provide the most viable product, defined as the product with the lowest return rate and highest sales.

Question

Assisting AI · Accepted Answer

To get the second highest and second lowest sales items, aggregate sales per product and use window functions to rank them:

```sql
SELECT product_name
FROM (
  SELECT product_name,
         SUM(Sale_Quantity) AS total_sales,
         RANK() OVER (ORDER BY SUM(Sale_Quantity) DESC) AS rank_desc,
         RANK() OVER (ORDER BY SUM(Sale_Quantity) ASC) AS rank_asc
  FROM sales_table
  GROUP BY product_name
) AS ranked
WHERE rank_desc = 2 OR rank_asc = 2;
```

For the most viable product (lowest return rate and highest sales), compute return rate and order accordingly:

```sql
SELECT product_name
FROM (
  SELECT product_name,
         SUM(Sale_Quantity) AS total_sales,
         SUM(Return) AS total_return,
         SUM(Return) / NULLIF(SUM(Sale_Quantity),0) AS return_rate
  FROM sales_table
  GROUP BY product_name
) AS metrics
ORDER BY return_rate ASC, total_sales DESC
LIMIT 1;
```

Both queries run in O(n) time where n is the number of rows, as they require a single scan and grouping. The window functions add negligible overhead. The result set contains the desired products.

Provide the item with the second highest and second lowest sales. Also provide the most viable product, defined as the product with the lowest return rate and highest sales.

💡 Model Answer

🎤 Get questions like this answered in real-time