Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using JOIN and Aggregate Window Function

Explore how to combine JOIN statements with aggregate window functions in SQL to retrieve summarized and related data. Understand partitioning, aliasing, and query optimization techniques essential for intermediate-level SQL interviews.

We'll cover the following...

Solution

The solution is given below:

MySQL
/*Query to list product's price and the maximum price within its category*/
SELECT P.PName AS 'Product Name',
       PC.CategoryName AS 'Category',
       P.Price,
       MAX(P.Price) OVER (PARTITION BY P.CategoryID) AS 'Max Category Price'
FROM   Products AS P
JOIN   ProductCategories AS PC ON P.CategoryID = PC.CategoryID;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–5: The SELECT statement selects the columns PName, Category, and the maximum price of the product in each category using the aggregate window function. We use AS to set an alias for the columns and tables.

  • Line 6: The data is retrieved from the Products table.

  • Line 7: The JOIN is performed between Products and ProductCategories tables on the CategoryID columns in both tables.

Recalling relevant concepts

We have covered the following concepts in this question:

  • Selective columns

  • Aliases

  • About joins

  • Aggregate functions

  • Window functions

Let’s discuss ...