You have an Azure Synapse Analytics dedicated SQL pool that contains a table named dbo.DimProduct. You need to write a query to meet the following requirements: Return the ranked position of each…

This question tests knowledge of T-SQL window functions, specifically RANK() for ordered ranking with gaps after ties, and NTILE(4) for quartile partitioning - both scoped with PARTITION BY product category.

Query and transform data

Question

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named dbo.DimProduct. You need to write a query to meet the following requirements:
Return the ranked position of each product ordered by list price descending within each product category, allowing gaps in the ranking values after ties.
Specify the quartile in which each product's list price falls within each product category.
How should you complete the query? To answer, select the appropriate options in the answer area.

Explanation

Approach. Use RANK() OVER (PARTITION BY ProductCategory ORDER BY ListPrice DESC) to assign ranked positions per category where tied prices receive the same rank and the next rank skips accordingly (e.g., 1,1,3). Use NTILE(4) OVER (PARTITION BY ProductCategory ORDER BY ListPrice DESC) to divide each category's products into four equal buckets (quartiles). Both functions must use PARTITION BY to reset calculations per product category, and ORDER BY ListPrice DESC to sort from highest to lowest price. DENSE_RANK() would be wrong here because it does NOT allow gaps after ties - the question explicitly requires gaps.

Concept tested. T-SQL window functions: RANK() vs DENSE_RANK() vs ROW_NUMBER() for ranking with/without gaps, and NTILE(n) for bucket/quartile distribution - all using OVER (PARTITION BY ... ORDER BY ...) syntax in Azure Synapse Analytics dedicated SQL pools.

Reference. https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql and https://learn.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql

Topics

#SQL Window Functions#Ranking Functions#NTILE#Azure Synapse Analytics SQL Pool

Community Discussion

No community discussion yet for this question.

Full DP-500 Practice