Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#standardSQL
SELECT
event_key,
COUNT(*) as unicorn_count
FROM (
SELECT
__key__.name as match_key,
event.name as event_key,
JSON_EXTRACT_SCALAR(alliances_json, '$.red.score') as red_score,
JSON_EXTRACT_SCALAR(alliances_json, '$.blue.score') as blue_score,
JSON_EXTRACT_SCALAR(score_breakdown_json , '$.red.kPaRankingPointAchieved') as red_kpa,
JSON_EXTRACT_SCALAR(score_breakdown_json , '$.red.rotorRankingPointAchieved') as red_rotor,
JSON_EXTRACT_SCALAR(score_breakdown_json, '$.blue.kPaRankingPointAchieved') as blue_kpa,
JSON_EXTRACT_SCALAR(score_breakdown_json, '$.blue.rotorRankingPointAchieved') as blue_rotor
FROM `tbatv-prod-hrd.the_blue_alliance.match`
WHERE comp_level = 'qm'
)
WHERE (red_score > blue_score AND red_kpa = 'true' AND red_rotor = 'true') OR (blue_score > red_score AND blue_kpa = 'true' AND blue_rotor = 'true')
GROUP BY event_key
ORDER BY unicorn_count DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment