SQL filter query results based on analytic function

I’d like to find an efficient way to filter my RANK() OVER function in SQL.

I have the following query:

SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
FROM
    `my_table` base
GROUP BY
    1

Which returns this result set:

Now I’d like to filter for items where the SLS_rank is < 10 OR the txn_rank is < 10. Ideally I’d like to do this in the HAVING clause, like this:

SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
FROM
    `my_table` base
GROUP BY
    1
HAVING   
    SLS_rank < 10 OR txn_rank < 10

But bigquery throws an error:

Column SLS_rank contains an analytic function, which is not allowed in HAVING clause at [9:8]

The only option I can think of is to create this as a separate table and selecting from there, but that doesn’t seem very pretty. Any other ideas on how to do this?

Answer

Update June 2021.

BigQuery announced support for the QUALIFY clause on the 10th of May, 2021.

The QUALIFY clause filters the results of analytic functions. An analytic function is required to be present in the QUALIFY clause or the SELECT list.

What you need can be achieved with QUALIFY in the following way:

SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
FROM `my_table` base
GROUP BY 1
QUALIFY SLS_rank < 10 OR txn_rank < 10

Find more examples in the documentation.