Exam DP-800 Topic 2 Question 85 Discussion

Explanation:
* CustomerId extraction # JSON_VALUE
* Ranking function # ROW_NUMBER
* Final filter # 1
The first selection is JSON_VALUE because CustomerId is stored as a scalar property in the JSON payload at
$.customer.id . Microsoft documents that JSON_VALUE extracts a scalar value from JSON text, whereas OPENJSON is used to shred JSON into relational rows/columns.
The second selection is ROW_NUMBER . The query partitions the data by:
CAST(t.CreatedAt AS date),
JSON_VALUE(t.Payload, ' $.customer.id ' )
and orders each partition by:
t.CreatedAt ASC
Microsoft documents that ROW_NUMBER() assigns sequential numbers starting at 1 within each partition .
Therefore, the earliest ticket for each customer on each day receives rn = 1 .
The third selection is therefore 1 , because filtering with:
WHERE rn = 1
returns exactly one row per customer per calendar day-the earliest ticket in that partition.
So the completed logic is:
WITH TicketRanks AS
(
SELECT
t.TicketId,
CAST(t.CreatedAt AS date) AS TicketDate,
JSON_VALUE(t.Payload, ' $.customer.id ' ) AS CustomerId,
t.CreatedAt,
ROW_NUMBER() OVER
(
PARTITION BY
CAST(t.CreatedAt AS date),
JSON_VALUE(t.Payload, ' $.customer.id ' )
ORDER BY t.CreatedAt ASC
) AS rn
FROM dbo.SupportTickets AS t
WHERE t.CreatedAt > = DATEADD(day, -7, SYSUTCDATETIME())
)
SELECT
TicketDate,
CustomerId,
TicketId,
CreatedAt
FROM TicketRanks
WHERE rn = 1
ORDER BY TicketDate, CustomerId;
Final hotspot selections:
* First dropdown: JSON_VALUE
* Second dropdown: ROW_NUMBER
* Third dropdown: 1