ROW_NUMBER

Syntax

ROW_NUMBER() OVER window

AMC specifics

• The function takes no arguments but needs the OVER clause with ORDER BY.
• Unlike RANK and DENSE_RANK, ROW_NUMBER assigns different numbers even to equal values.

Example

The five search terms with the most clicks per campaign.

WITH by_term AS (
  SELECT
    campaign,
    customer_search_term,
    SUM(clicks) AS clicks
  FROM
    sponsored_ads_traffic
  GROUP BY
    1,
    2
),
ranked AS (
  SELECT
    campaign,
    customer_search_term,
    clicks,
    ROW_NUMBER() OVER (PARTITION BY campaign ORDER BY clicks DESC) AS rn
  FROM
    by_term
)
SELECT
  campaign,
  customer_search_term,
  clicks
FROM
  ranked
WHERE
  rn <= 5

Checked against the AMC schema, not executed on an AMC instance.