Marketing Data Extension `Last Click Date` from data views

Question

I have a query that's intended to update a field named Last_Click_Date based on the most recent EventDate from the _Click data view.

It doesn't appear to be grabbing the most recent date param and appending the subscriber record in the marketing data extension.

Here's query -- wondering why it would essentially be pulling a non-most recent click date:

SELECT de1.*
            FROM (SELECT 
                    S.SubscriberKey
                    , S.EmailAddress as 'email'
                    /*, S.Status*/
                    , Max(cast(C.EventDate as DateTime)) AS 'last_click_date'
                    , ROW_NUMBER() OVER (PARTITION BY
                        S.SubscriberKey
                            ORDER BY S._CustomObjectKey DESC) rn
                FROM [Subscribers Data View] S
                INNER JOIN [Click Data View] C WITH (NOLOCK)
                    on S.SubscriberKey = C.SubscriberKey
            GROUP BY S._CustomObjectKey, S.SubscriberKey, S.EmailAddress, S.Status, C.EventDate
    ) de1
WHERE de1.rn = 1

Answer 1

I edited your query a bit and was able to get accurate results using the below query. I believe you went a bit more complex than you needed to with it, which may have been messing with your results:

            SELECT 
                S.SubscriberKey
                , S.EmailAddress as 'email'
                , c.last_click_date
            FROM [_Subscribers] S
            INNER JOIN (
                SELECT Cl.SubscriberKey
                , cast(Max(Cl.EventDate) as DateTime) AS 'last_click_date'
                FROM [_Click] Cl WITH (NOLOCK)
                GROUP BY Cl.SubscriberKey
            ) c
                on S.SubscriberKey = c.SubscriberKey

This returned the max date (you honestly don't even need the cast there as it is stored in datetime in the dataview) of the last click for each subscriber was displayed in my DE.

If you have a large audience, you may need to look at splitting this up or otherwise optimizing it as data views are notoriously slow. (e.g. WHERE Cl.EventDate >= DATEADD(month,-1, GETDATE()) to get only clicks in the last month)