20 Sep 19 · npack ·       Add to Favorites  

RANK() OVER PARTITON BY Syntax in Bigquery

Use the RANK() over analytic functions to Calculate RANK. Analytic functions are evaluated after aggregation (GROUP BY and non-analytic aggregate functions)

The following query calculates the rank of each employee within their department:

SELECT firstname, department, startdate,
RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

you can extend this query to only see the top users,

SELECT firstname, department, startdate,
RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees where rank=1;

npack

posted on 20 Sep 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied