SQL Exercise – 19

SQL Exercise:

Retrieve the Top and Bottom 2 Sales

Scenario:

You have a SalesData table with the following columns:

• SalesID (unique identifier for sales records)
• Category (e.g., Electronics, Furniture)
• Sales (sales value)

The task is to:

1. Retrieve the top 2 highest sales and bottom 2 lowest sales for each category.
2. Handle ties so that if two sales have the same value, both are included.

Create table script:

CREATE TABLE SalesData (
SalesID INT PRIMARY KEY,
Category VARCHAR(50),
Sales DECIMAL(10, 2)
);

Data Insertion script:

INSERT INTO SalesData (SalesID, Category, Sales)
VALUES
(1, 'Electronics', 5000.00),
(2, 'Electronics', 7000.00),
(3, 'Electronics', 7000.00),
(4, 'Electronics', 6000.00),
(5, 'Furniture', 4000.00),
(6, 'Furniture', 4500.00),
(7, 'Furniture', 3500.00),
(8, 'Furniture', 4000.00);

Solution:

 WITH Sales AS (
SELECT 
SalesID,
Category,
Sales,
RANK() OVER (PARTITION BY Category ORDER BY Sales DESC) AS TopRank,
RANK() OVER (PARTITION BY Category ORDER BY Sales ASC) AS BottomRank
FROM SalesData
)
SELECT 
SalesID,
Category,
Sales,
CASE 
WHEN TopRank <= 2 THEN 'Top'
WHEN BottomRank <= 2 THEN 'Bottom'
END AS RankType
FROM Sales
WHERE TopRank <= 2 OR BottomRank <= 2
ORDER BY Category, RankType, Sales DESC;

Output:

 

Explanation:

1. DENSE_RANK():
   • It ranks the data without gaps, even if there are ties. For example, if two sales have the same value, both will receive the same rank, and the next rank will continue from the next consecutive number.
2. PARTITION BY Category:
   • This groups the data by the Category, so rankings are calculated independently within each category.
3. ORDER BY Sales DESC for Top Sales:
   • Ranks the sales in descending order for the top 2 highest sales.
4. ORDER BY Sales ASC for Bottom Sales:
   • Ranks the sales in ascending order for the bottom 2 lowest sales.
5. Rank <= 2:
   • Filters to include only the top 2 and bottom 2 sales for each category.