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.
WITH TicketRanks AS
SELECT
t. TicketId,
CAST(t.Createdit AS date) AS TicketDate,
_________________ (t.Payload, "S.customer.id*) AS CustomerId,
t. CreatedAt,
_________________ () OVER
(
PARTITION BY
CAST(t.CreateAT AS date),
JSON_VALUE (t. Payload, "S.customer.id')
ORDER BY t.CreatedAt ASC
) AS rn
FROM dbo.SupportTickets AS t
WHERE t.CreatedAt > =DATEADO(day, -7, SYSUTDATETIME())
)
SELECT
TicketDate,
CustomerId,
TicketId,
CreatedAt
FROM TicketRanks
WHERE rn = _________________
ORDER BY TicketDate, CustomerId;
收藏
