SQL Interview Preparation—Intermediate Level/

...

/

Answer: Using JOIN and Aggregate Window Function

Answer: Using JOIN and Aggregate Window Function

Find a detailed explanation of using JOIN and the aggregate window function.

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 ...