Exam DP-500 topic 1 question 156 discussion

Actual exam question from Microsoft's DP-500

Question #: 156
Topic #: 1

HOTSPOT
-

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.

NOTE: Each correct selection is worth one point.

Show Suggested Answer

Suggested Answer:

by MaryemSB at Dec. 20, 2023, 3:12 p.m.

Comments

Submit Cancel

Momoanwar

1 year, 5 months ago

Chatgpt: To complete the query according to the requirements: - To return the ranked position of each product ordered by list price descending within each product category, allowing gaps in the ranking values after ties, you should use `RANK()` function because it will assign the same rank to tied values, with the next values receiving a rank that includes the gap (e.g., 1, 2, 2, 4). - To specify the quartile in which each product's list price falls within each product category, you should use `NTILE(4)` function because it will divide the rows into four equal groups and assign a quartile number to each row. The final selections should be: - For ranking: `RANK() OVER (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank` - For quartile: `NTILE(4) OVER (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile`

upvoted 1 times

...

MaryemSB

1 year, 6 months ago

Correct answer

upvoted 1 times

...