Query for Subscribers Click Counts and SendLog

Question

I'm in the process of building a query that will give me a count of all links a subscriber has clicked on from recent sends. I have a query written out using the salesforce documentation, and I have tried multiple variations. I keep getting the unhelpful error "Automation failed due to system error". Would anyone be able to tell me where I am going wrong with my approach?

Link to salesforce doc below

https://help.salesforce.com/s/articleView?id=sf.mc_as_query_find_subscribers_with_no_opens_or_clicks_ref.htm&type=5

select s.subscriberkey,
s.jobid,
s.batchid,
s.eventdate As SendDate,
c.LinkName as URL_ID,
c.LinkContent as URL,
c.subscriberid as SubID
    From [_sent] as s
        left join [_open] as o
            on s.jobid = o.jobid
            and s.listid = o.listid
            and s.batchid = o.batchid
            and s.subscriberid = o.subscriberid
            and o.isunique = 1
        left join [_click] as c
            on s.jobid = c.jobid
            and s.listid = c.listid
            and s.batchid = c.batchid
            and s.subscriberid = c.subscriberid
            and c.isunique = 1
    WHERE  ( o.subscriberid IS not NULL
         AND c.subscriberid IS not NULL )
         and s.eventdate >= DATEADD(day, -7, CURRENT_TIMESTAMP)

Answer 1

I have never used CURRENT_TIMESTAMP in my queries, I'm not sure if it's supported in SFMC. You should try using GETDATE() function instead.

Please note that this function returns date in SFMC server time (GMT-6), but since you query a data view which also uses same timezone in its dates, you don't need to convert it.