SQL Query From Send Data View looking for unique SubscriberKey

Question

I'm trying to run a query to load data into a data extension. It basically pulls sends from the last x number of days from the Sent data view table. I'm trying to just have a record populate for a unique subscriber key from the records most recent send. Here is my query below:

    SELECT
          sent.AccountID
        , sent.OYBAccountID
        , sent.JobID
        , sent.ListID
        , sent.BatchID
        , sent.SubscriberID
        , DISTINCT sent.SubscriberKey
        , sent.EventDate
        , sent.Domain
        , sent.TriggererSendDefinitionObjectID
        , sent.TriggeredSendCustomerKey
        , j.EmailName

FROM _Sent AS sent
LEFT JOIN ENT._subscribers AS sub
    ON sub.SubscriberKey = sent.SubscriberKey
LEFT JOIN _job AS j
    ON j.JobID = sent.JobID
WHERE DATEDIFF(day,sent.EventDate,GETDATE()) between 0 and 61

How can I get this table to show a list of my subscribers that have received an email in the past 60 days? I just want unique subscriber keys and the most recent email they have been sent.

Answer 1

This will return the most recent send for each subscriber key in the past 61 days:

SELECT top 1 with ties
  sent.AccountID
, sent.OYBAccountID
, sent.JobID
, sent.ListID
, sent.BatchID
, sent.SubscriberID
, sent.SubscriberKey
, sent.EventDate
, sent.Domain
, sent.TriggererSendDefinitionObjectID
, sent.TriggeredSendCustomerKey
, j.EmailName
FROM _Sent AS sent
LEFT JOIN ENT._subscribers AS sub ON sub.SubscriberKey = sent.SubscriberKey
LEFT JOIN _job AS j ON j.JobID = sent.JobID
WHERE sent.eventDate >= convert(date, getDate()-61)
order by row_number() over (partition by sent.subscriberkey order by sent.eventDate desc)

The order by partition assigns a number for each subscriberkey and the rows are ordered by eventDate descending. The top 1 with ties just returns the first for each.

There's a long-form example here on my blog: https://sprignaturemoves.com/picking-rows-with-partitions/