Query works now fine but I'd like to make it a little bit more complex. In my linkname, I've got a special nomenclature for banners which is "banner1_bannername" or "banner2_bannername". So I only want to rank my link by bannername.
This is the query I managed to make:
SELECT subscriberkey, bannername FROM (SELECT subscriberkey, bannername, ROW_NUMBER() OVER (PARTITION BY subscriberkey ORDER BY freq DESC) AS rn FROM ( SELECT subscriberkey, bannername, COUNT('x') AS freq FROM (SELECT subscriberkey, substring(linkname,9,99) as bannername FROM _click) frequency GROUP BY subscriberkey, bannername) ranked) sub WHERE sub.rn = 1
It looks like SalesForce validated the query syntax but it doesn't work when running in my data extension with subscriberkey and bannername column. The bannername stays empty even though subscriberkey populates.
END OF EDIT
I'd like to know what are the most clicked link for each of my user. I thought I could do that querying the _click table.
This is where I got so far:
SELECT subscriberkey, linkname AS most_clicked_link FROM (SELECT subscriberkey, linkname, ROW_NUMBER() OVER (PARTITION BY subscriberkey ORDER BY freq DESC) AS rn FROM ( SELECT subscriberkey, linkname, COUNT('x') AS freq FROM _click GROUP BY 1, 2) link_freq) ranked_link WHERE rn = 1
However it doesn't seem to fit in SalesForce, I've got errors messages like:
Old style JOIN (ANSI JOINs) syntax is not allowed
I know that some syntaxes are not support by SalesForce but I'm too much a novice to detect what's wrong there, any help?