Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- id STRING
- group STRING
- score INTEGER
- WITH users_with_positions AS (
- SELECT
- id,
- group,
- ROW_NUMBER() OVER (ORDER BY score DESC) AS score_position
- FROM gbq.users
- WHERE group = 'group_1'
- )
- SELECT
- id,
- group,
- (
- CASE
- WHEN position > ranks.high_threshold THEN 'high'
- WHEN position > ranks.medium_threshold THEN 'med'
- ELSE 'low'
- ) AS rank
- FROM users_with_positions, (
- SELECT
- (total_positions / 3 * 2) AS high_threshold,
- (total_positions / 3) AS medium_threshold
- FROM (
- SELECT
- MAX(score_position) AS total_positions
- FROM users_with_positions
- )
- )
Add Comment
Please, Sign In to add comment