How Do You Return One Earliest Ticket per Customer per Day from JSON Data?
HOTSPOT
You have an Azure SQL database that contains a table named dbo.SupportTickets. dbo.SupportTickets contains a JSON column named Payload and a datetime column CreatedAt.
You need to generate a report for the last seven days that meets the following requirements:
• Returns exactly one row per customer per day
• For each customer and day, returns the earliest ticket
• Includes the customer ID stored in Payload
How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Community Insight
The item tests whether you can combine JSON shredding with a windowed ranking to de-duplicate a time-series table; the trap is reaching for GROUP BY with MIN(CreatedAt), which cannot return the single earliest ticket row and its other columns.
This hotspot item asks you to complete a T-SQL query over dbo.SupportTickets that returns exactly one row per customer per day — the earliest ticket — with the customer ID read out of the JSON Payload column. The page confirms the three selections: JSON_VALUE to extract the customer ID, ROW_NUMBER to rank tickets inside each customer/day partition, and a filter of 1 on that ranking.
Candidates often answer with GROUP BY customer/day plus MIN(CreatedAt) or with DISTINCT, believing that collapses duplicates — but neither guarantees one row per customer per day carrying the actual earliest ticket, and MIN() discards the rest of that ticket's columns.
Community Discussion (3 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
The query must do two distinct jobs: read the customer identity out of the Payload JSON column, and pick exactly one ticket row per customer per calendar day. JSON_VALUE(Payload, '$.customerId') is the correct scalar extractor because it returns a single nvarchar value from a JSON property and can be used in SELECT, PARTITION BY and ORDER BY clauses. ROW_NUMBER OVER (PARTITION BY <customer>, CAST(CreatedAt AS date) ORDER BY CreatedAt) numbers each ticket within its customer/day group, so ordering ascending puts the earliest ticket at position 1. Wrapping the query in a CTE or derived table and filtering on the row number equal to 1 yields exactly one row per customer per day and preserves the full earliest ticket row. The last seven days are then handled by a WHERE predicate on CreatedAt, and the ASC sort direction is what makes the lowest row number correspond to the earliest timestamp.Why Other Candidate Answers Are Wrong
GROUP BY with MIN(CreatedAt) returns the correct timestamp but cannot project the other columns of that specific ticket, so it does not satisfy "returns the earliest ticket". DISTINCT or a plain SELECT DISTINCT collapses nothing, because every ticket row is naturally unique. Using JSON_QUERY instead of JSON_VALUE returns a JSON fragment rather than a scalar customer ID, so it will not compare or group cleanly. Choosing a filter of 0, or omitting the ranking filter entirely, returns every ticket instead of only the earliest, and ordering by CreatedAt DESC inside the window would keep the latest ticket rather than the earliest.Community Comment Notes
Multiple learners converged on the same three-part recipe: JSON_VALUE for the customer ID, ROW_NUMBER for the per-day ranking, and a final filter equal to 1 — as gangreja summarised in his two comments recommending exactly those selections. Skri113xX reasoned that some of the offered constructs could be eliminated outright and that the strongest remaining choice was the one built on JSON_VALUE, which matches the vendor-documented scalar extraction behaviour. The consensus across these comments is consistent with the wording of the requirement rather than with any single vote count.Official Reference
Exam Strategy
Read the requirement verbs literally: "exactly one row per customer per day" plus "the earliest ticket" is the classic ROW_NUMBER + filter 1 pattern, while "a value from JSON" means JSON_VALUE, not JSON_QUERY. Confirm the ORDER BY direction is ASC before you commit, because DESC silently flips the answer to the latest ticket.
Frequently Asked Questions
Why does GROUP BY with MIN(CreatedAt) fail for the dbo.SupportTickets per-customer-per-day report?
It returns the earliest timestamp per group but cannot return the other columns of that specific ticket, so it never satisfies the requirement to return the earliest ticket row itself.
Should the ROW_NUMBER window use JSON_VALUE(Payload,'$.customerId') and CAST(CreatedAt AS date) in the PARTITION BY?
Yes. Partitioning by the extracted customer ID and the date, then ordering by CreatedAt ascending, makes row number 1 the earliest ticket for that customer on that day.