Pass Variable from View in LIMIT clause in Bigquery

Question

I need to create a variable which will store the number of users to be filtered. Basically, I need to check how many events a user generates within a certain number of months and then only the top number of users are used. I noticed that Bigquery does not have a set variable functionality, therefore I created a view which has my variables.

I have the following Bigquery SQL code.

WITH vars AS (
  SELECT 3 as num_months,
         200000 as num_users
)

SELECT id, count(d.value) as count_value
FROM events_data evt, unnest(evt.data) AS d, vars
WHERE  DATE(eventdate) > date_sub(current_date(), INTERVAL num_months MONTH) AND d.key='eventid'
GROUP BY id
ORDER BY count_value
LIMIT num_users

The use of num_months works perfectly within the filtering of the number months however when I try to limit the number of users within the LIMIT clause it gives the following error

Syntax error: Unexpected identifier "num_users"

I have even tried referencing the view as well like vars.num_users

Answer 1

Use row_number():

WITH params AS (
      SELECT 3 as num_months, 200000 as num_users
     )
SELECT *
FROM (SELECT id, COUNT(*) as count_value,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as seqnum
      FROM events_data evt, unnest(evt.data) d JOIN
           params
           ON DATE(eventdate) > date_sub(current_date(), INTERVAL num_months MONTH) AND
      WHERE d.key = 'eventid'
      GROUP BY id
     ) id CROSS JOIN
     params
WHERE seqnum <= params.num_users
ORDER BY count_value;

I typically call such CTEs params, so I renamed it.