Posted on February 7, 2023 by Kevin Guisarde .

The SQL Partition By Clause is a crucial tool in managing and analyzing large datasets in SQL Server and other relational database management systems. By leveraging this feature, it becomes possible to segment a vast result set into more manageable portions based on one or more columns in the data. This can lead to significant improvements in query execution times and make it easier to perform in-depth data analysis. Whether you are working with massive datasets or need to optimize your query performance, the SQL Partition By Clause is a powerful tool that can help you achieve your goals.

Agenda

1. Introduction to SQL Partition By Clause
2. Different Concept Types in SQL Partition By Clause
3. Real-World Example Questions in Pharmaceutical Industry
4. Most Commonly Asked Interview Question
5. Conclusion

Introduction to SQL Partition By Clause

SQL Partition By Clause is an important concept in SQL Server and other relational database management systems. It is used to divide a large result set into smaller, manageable parts based on one or more columns in the data. This can improve the performance of query execution and make it easier to analyze data.

Different Concept Types in SQL Partition By Clause

There are several different types of partitions that can be created using the Partition By clause. Let’s look at each of these in detail, using examples from the Pharmaceutical industry.

Row Number Partition

This type of partition assigns a unique row number to each row in the result set. This is useful for pagination, as you can retrieve a specific range of rows based on the row number.

SELECT ROW_NUMBER() OVER (PARTITION BY ProductName ORDER BY SaleDate) AS RowNumber,     
  ProductName, 
  SaleDate, 
  SaleAmount 
FROM Sales 
WHERE Industry = 'Pharmaceutical'

In the above example, we are using the Row Number partition to assign a unique row number to each row in the result set. The partition is based on the ProductName column and the rows are ordered by SaleDate.

Rank Partition

This type of partition assigns a rank to each row in the result set, based on one or more columns. Rows with the same values will receive the same rank.

SELECT RANK() OVER (PARTITION BY ProductName ORDER BY SaleAmount DESC) AS Rank, 
  ProductName, 
  SaleDate, 
  SaleAmount 
FROM Sales 
WHERE Industry = 'Pharmaceutical'

In the above example, we are using the Rank partition to assign a rank to each row in the result set. The partition is based on the ProductName column and the rows are ordered by SaleAmount in descending order.

Dense Rank Partition

This type of partition assigns a dense rank to each row in the result set, based on one or more columns. Rows with the same values will receive the same rank, and there will not be any gaps in the ranks.

SELECT DENSE_RANK() OVER (PARTITION BY ProductName ORDER BY SaleAmount DESC) AS DenseRank,       
  ProductName, 
  SaleDate, 
  SaleAmount 
FROM Sales 
WHERE Industry = 'Pharmaceutical'

In the above example, we are using the Dense Rank partition to assign a dense rank to each row in the result set. The partition is based on the ProductName column and the rows are ordered by SaleAmount in descending order.

Real-World Example Questions in Pharmaceutical Industry

Before we move on to the example questions, let’s create the script to generate the table and records needed to answer the questions.

-- Script to create tables and data for Real World Example Questions

-- Table to store the Product Information
CREATE TABLE Product_Information (
    Product_ID INT PRIMARY KEY,
    Product_Name VARCHAR(100) NOT NULL,
    Manufacturer_ID INT NOT NULL,
    Product_Type VARCHAR(100) NOT NULL,
    Product_Launch_Date DATE NOT NULL
);

-- Table to store the Sales Information
CREATE TABLE Sales_Information (
    Sales_ID INT PRIMARY KEY,
    Product_ID INT NOT NULL,
    Sales_Date DATE NOT NULL,
    Sales_Quantity INT NOT NULL,
    Sales_Amount DECIMAL(18,2) NOT NULL
);

-- Insert Data into Product_Information
INSERT INTO Product_Information (Product_ID, Product_Name, Manufacturer_ID, Product_Type, Product_Launch_Date)
VALUES (1, 'Lipitor', 101, 'Cholesterol Lowering', '2020-01-01'),
       (2, 'Advil', 102, 'Pain Relief', '2020-01-01'),
       (3, 'Zocor', 101, 'Cholesterol Lowering', '2020-02-01'),
       (4, 'Aleve', 102, 'Pain Relief', '2020-02-01'),
       (5, 'Crestor', 103, 'Cholesterol Lowering', '2020-03-01'),
       (6, 'Tylenol', 102, 'Pain Relief', '2020-03-01');

-- Insert Data into Sales_Information
INSERT INTO Sales_Information (Sales_ID, Product_ID, Sales_Date, Sales_Quantity, Sales_Amount)
VALUES (1, 1, '2021-01-01', 100, 1000),
       (2, 1, '2021-02-01', 120, 1200),
       (3, 1, '2021-03-01', 130, 1300),
       (4, 2, '2021-01-01', 50, 500),
       (5, 2, '2021-02-01', 60, 600),
       (6, 2, '2021-03-01', 70, 700),
       (7, 3, '2021-01-01', 200, 2000),
       (8, 3, '2021-02-01', 220, 2200),
       (9, 3, '2021-03-01', 240, 2400),
       (10, 4, '2021-01-01', 80, 800),
       (11, 4, '2021-02-01', 90, 900),
       (12, 4, '2021-03-01', 100, 1000),
       (13, 5, '2021-01-01', 150, 1500),
       (14, 5, '2021-02-01', 170, 1700),
       (15, 5, '2021-03-01', 190, 1900),
       (16, 6, '2021-01-01', 60, 600),
       (17, 6, '2021-02-01', 70, 700),
       (18, 6, '2021-03-01', 80, 800);

1. What is the average salary of pharmaceutical sales representatives grouped by city?

WITH Sales_Data AS (
  SELECT
    City,
    Salary,
    AVG(Salary) OVER (PARTITION BY City) AS Average_Salary
  FROM
    Pharmaceutical_Sales_Representatives
)
SELECT
  City,
  Average_Salary
FROM
  Sales_Data
GROUP BY
  City,
  Average_Salary;

2. How many pharmaceutical products were sold in each state in the last 5 years?

WITH Sales_Data AS (
  SELECT
    State,
    Year,
    SUM(Products_Sold) OVER (PARTITION BY State) AS Total_Products_Sold
  FROM
    Pharmaceutical_Product_Sales
  WHERE
    Year >= YEAR(GETDATE() - 5)
)
SELECT
  State,
  Total_Products_Sold
FROM
  Sales_Data
GROUP BY
  State,
  Total_Products_Sold;

3. What is the total cost of pharmaceutical products sold in each city over the last 10 years?

WITH Sales_Data AS (
  SELECT
    City,
    Year,
    SUM(Product_Cost) OVER (PARTITION BY City) AS Total_Cost
  FROM
    Pharmaceutical_Product_Sales
  WHERE
    Year >= YEAR(GETDATE() - 10)
)
SELECT
  City,
  Total_Cost
FROM
  Sales_Data
GROUP BY
  City,
  Total_Cost;

Most Commonly Asked Interview Question and Answer

Q: Can you explain the use of Over(Partition By) clause in SQL?

A: The Over(Partition By) clause is a function in SQL that allows you to perform a calculation over a set of rows that are defined by a partition. In other words, the Partition By clause allows you to divide the rows of a result set into groups based on the values in one or more columns.

For example, in a previous project, I had to analyze sales data for a pharmaceutical company. I used the Over(Partition By) clause to group the sales data by city and calculate the average salary of pharmaceutical sales representatives for each city. This allowed me to easily identify the cities with the highest and lowest average salaries.

In summary, the Over(Partition By) clause is a powerful tool for data analysis and can be used in a variety of scenarios, such as calculating running totals, moving averages, and percentiles.

This gave us a clear picture of the cost of each medication and helped us make informed decisions about which medications to prescribe to our patients.

Conclusion

In this blog, we covered the SQL Partition By Clause and its uses in the pharmaceutical industry. We went through different concept types and provided real-world examples and coding exercises to help you understand how to use the Over(Partition By) clause in SQL. Finally, we discussed a commonly asked interview question and provided a detailed answer to help you prepare for your next interview.