I am trying to query the _Click data view to find the top performing links in any given job. I have wrote my query to report back the top 3 links in every job, to be grouped by job id, link, then links clicked descending by counting the subscriber key.

SELECT c.JobID, c.LinkContent, count(c.SubscriberKey) FROM _Click c WHERE ( SELECT count(AccountID) FROM _Click as d WHERE d.LinkContent = c.LinkContent AND count(d.SubscriberKey) >= count(c.SubscriberKey) ) <=3 ORDER BY c.JobID, c.LinkContent, clicked DESC

This code generates syntax error requiring that I have TOP in my select statement which I don't want because I have no idea how many rows (JobID) it will return.

I also tried removing the ORDER BY line but it generates a new syntax code stating that I cannot use an aggregrate in the WHERE clause. For those curious, I adapted this code from: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/


SFMC runs on SQL Server, so your MySQL script isn't going to work. Needs to be a T-SQL query.

Something like this should work:

, y.linkcontent
, y.counter
, y.ranking
from (
  , x.linkcontent
  , x.counter
  , dense_rank() over (partition by x.jobid order by x.counter desc) ranking
  from (
    , c.linkcontent
    , count(c.subscriberkey) counter
    from _Click c
    group by c.jobid, c.linkcontent
  ) x
) y
where y.ranking <= 3
order by y.jobid, y.ranking asc

The innermost sub-query will total clicks by jobid and linkcontent. The query outside of that will rank the rows by number of clicks. The outermost will limit it to the top 3 for each job and link.

  • Thank you explaining how I went wrong. I am new to SQL and SFMC and did not realize how fundamentally different they are. FYI to others, I needed to modify Adam's answer by adding a codeTOPcode after codeSELECTcode to make SFMC happy and I also changed my criteria to get exactly the results I was looking for. Adam provided the core logic.
    – Minh D
    Oct 6 '15 at 20:37
  • @Adam Spriggs why did you count subscriberkey and not linkcontent?
    – garek007
    Dec 1 '17 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.