DP-800: Developing AI-Enabled Database Solutions

Question

You have an Azure SQL database containing the dbo.SupportTickets table. dbo.SupportTickets has a JSON column named Payload and a datetime column named CreatedAt.

Generate a report for the last seven days that meets the following requirements:

Return exactly one row for each customer per day.
For each customer and day, return the earliest ticket.
Include the customer ID stored in Payload.

How should you complete the Transact-SQL query?

Select

WITH TicketRanks AS
(
    SELECT
        t.TicketId,
        CAST(t.CreatedAt AS date) AS TicketDate,
        (t.Payload, '$.customer.id') AS CustomerId,
        t.CreatedAt,
        () 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, SYSDATETIME())
)
SELECT
    TicketDate,
    CustomerId,
    TicketId,
    CreatedAt
FROM TicketRanks
WHERE rn = 
ORDER BY TicketDate, CustomerId;

Explanation

JSON_VALUE extracts a scalar value from the $.customer.id JSON path. ROW_NUMBER() assigns a unique sequential number within each customer-and-date partition; ordering by CreatedAt ASC assigns 1 to the earliest ticket. Filtering on rn = 1 therefore returns one earliest ticket for each customer per day.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ60

Community Discussion