Most clicked link by users using SQL query

Question

EDIT:
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.

Any ideas?

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?

Thanks alot!

Answer 1

There are two things that cause these to error:

1. GROUP BY 1, 2 : Variables are not supported in SFMC . You would have to add the name of the columns to group by.

A query activity SQL statement is an isolated statement and cannot take input parameters from other activities or processes.

2. rn = 1: you would have to reference this through the alias ranking_number.rn

Try this:

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 subscriberkey, linkname) link_freq) ranked_link
 WHERE ranked_link.rn = 1

hope this helps!

Resources:

• Query interaction page