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?