Guest User

Untitled

a guest
Dec 14th, 2017
60
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.61 KB | None | 0 0
  1. id STRING
  2. group STRING
  3. score INTEGER
  4.  
  5. WITH users_with_positions AS (
  6. SELECT
  7. id,
  8. group,
  9. ROW_NUMBER() OVER (ORDER BY score DESC) AS score_position
  10. FROM gbq.users
  11. WHERE group = 'group_1'
  12. )
  13.  
  14. SELECT
  15. id,
  16. group,
  17. (
  18. CASE
  19. WHEN position > ranks.high_threshold THEN 'high'
  20. WHEN position > ranks.medium_threshold THEN 'med'
  21. ELSE 'low'
  22. ) AS rank
  23. FROM users_with_positions, (
  24. SELECT
  25. (total_positions / 3 * 2) AS high_threshold,
  26. (total_positions / 3) AS medium_threshold
  27. FROM (
  28. SELECT
  29. MAX(score_position) AS total_positions
  30. FROM users_with_positions
  31. )
  32. )
Add Comment
Please, Sign In to add comment