Issue querying _Click table to get users clicking the same number of times in two or more different `LinkNames`

Question

I am having a hard time with a query that I did for Salesforce Marketing Cloud, and you might know where is the error. I am querying the table _Click, (table schema), and I want all my users that had cliked more than three times in a link with the same LinkNameI also want to see only the LinkName with the maximum number of clicks for each user. The query works pretty well, if I don't have the same user clicking the same number of times in two or more different LinkName, trying to avoid that I used the data EventDate and ask for the latest click date, but this is also not working.

Some important information: The data retrieve from the table _Click will be use to populate a table with the follow schema
Field name - EmailAddress > DataType - EmailAddress > Length - 254 > PrimaryKey - True
Field name - SubscriberID > DataType - Number > Length - not limited > PrimaryKey - False
Field name - LinkName > DataType - Text > Length - 100 > PrimaryKey - False
Field name - maxClicks > DataType - Number > Length - not limited > PrimaryKey - False
Field name - ClickDate > DataType - Date > Length - not limited > PrimaryKey - False

Query

SELECT DISTINCT
a.SubscriberID,a.maxClicks,a.LinkName,a.SubscriberKey as EmailAddress,a.ClickDate
FROM (SELECT SubscriberID,COUNT(LinkName) as maxClicks,LinkName,SubscriberKey,MAX(EventDate) as ClickDate
FROM _Click
WHERE convert(varchar(24), EventDate, 113) >= convert(varchar(24), getdate()-9, 113)
GROUP BY SubscriberID,SubscriberKey,LinkName) as a
LEFT JOIN (SELECT SubscriberID,COUNT(LinkName) as maxClicks,LinkName,SubscriberKey,MAX(EventDate) as ClickDate
FROM _Click
WHERE convert(varchar(24), EventDate, 113) >= convert(varchar(24), getdate()-9, 113)
GROUP BY SubscriberID,SubscriberKey,LinkName) as b
ON a.SubscriberID = b.SubscriberID AND a.maxClicks < b.maxClicks AND a.ClickDate < b.ClickDate
WHERE a.maxClicks > 3
AND convert(varchar(24), a.ClickDate, 113) >= convert(varchar(24), a.ClickDate, 113)
AND a.LinkName in ('Beach','Skiing','Nature','Wellness','Business','Shopping','Food_Culture','Fun')
AND b.maxClicks is NULL AND b.ClickDate IS NULL

Could you help me please?

Answer 1

Felipe, I'm not entirely certain I understand your requirements, but based on your description, it sounds like you could use the ROW_NUMBER function here to return only one row per subscriber. Something like this...

SELECT
  c.SubscriberID,
  c.EmailAddress,
  c.LinkName,
  c.ClickDate,
  c.NumClicks AS MaxClicks
FROM
  (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY SubscriberID ORDER BY COUNT(1) DESC) AS RowNumber,
      SubscriberID,
      SubscriberKey AS EmailAddress,
      LinkName,
      MAX(EventDate) AS ClickDate,
      COUNT(1) AS NumClicks
    FROM
      _Click WITH (NOLOCK)
    WHERE
      EventDate >= GETDATE() - 9 AND
      LinkName IS NOT NULL
    GROUP BY
      SubscriberID,
      SubscriberKey,
      LinkName
    HAVING COUNT(1) > 3
  ) AS c
WHERE
  c.RowNumber = 1

If you need one row per subscriber/linkname combination, then Partition by those two fields, rather than SubscriberID alone.