gistfile1.txt

SELECT 
  IF( rank IS NULL, top_keyword, keyword ) AS keyword, 
  IF( rank IS NULL, top_rank_date, rank_date ) AS rank_date, 
  IF( rank IS NULL, top_engine, engine ) AS engine, 
  IF( rank IS NULL, top_locale, locale ) AS locale, 
  IF( rank IS NULL, top_geo, geo ) AS geo, 
  IF( rank IS NULL, top_mobile, mobile ) AS mobile, 
  IF( rank IS NULL, top_micro_format, micro_format ) AS micro_format, 
  IF( rank IS NULL, top_news, news ) AS news, 
  IF( rank IS NULL, top_video, video ) AS video, 
  IF( rank IS NULL, top_integrated_places, integrated_places ) AS integrated_places, 
  IF( rank IS NULL, top_maps, maps ) AS maps, 
  IF( rank IS NULL, top_image, image ) AS image, 
  IF( rank IS NULL, top_blog_posts, blog_posts ) AS blog_posts, 
  IF( rank IS NULL, top_shopping, shopping ) AS shopping, 
  IF( rank IS NULL, top_rank, rank ) AS rank, 
  IF( rank IS NULL, top_ranking_url, ranking_url ) AS ranking_url, 
  IF( rank IS NULL, top_ranking_title, ranking_title ) AS ranking_title, 
  IF( rank IS NULL, top_ranking_indented, ranking_indented ) AS ranking_indented, 
  IF( rank IS NULL, top_ranking_authorship, ranking_authorship ) AS ranking_authorship, 
  IF( rank IS NULL, top_ranking_image, ranking_image ) AS ranking_image, 
  IF( rank IS NULL, top_ranking_news, ranking_news ) AS ranking_news, 
  IF( rank IS NULL, top_ranking_maps, ranking_maps ) AS ranking_maps, 
  IF( rank IS NULL, top_ranking_video, ranking_video ) AS ranking_video, 
  IF( rank IS NULL, top_ranking_shopping, ranking_shopping ) AS ranking_shopping, 
  IF( rank IS NULL, top_ranking_blog_posts, ranking_blog_posts ) AS ranking_blog_posts, 
  IF( rank IS NULL, top_ranking_micro_format, ranking_micro_format ) AS ranking_micro_format, 
  IF( rank IS NULL, top_ranking_page, ranking_page ) AS ranking_page,
  IF( rank IS NULL, top_top10, top10 ) AS top10
FROM 
(
SELECT keyword, rank_date, engine, locale, geo, mobile, micro_format, news, video, integrated_places, maps, image, blog_posts, shopping, 
serp.rank AS rank, serp.url AS ranking_url, serp.title AS ranking_title, serp.indented AS ranking_indented, serp.authorship AS ranking_authorship, serp.image AS ranking_image, serp.news AS ranking_news, serp.maps AS ranking_maps, serp.video AS ranking_video, serp.shopping AS ranking_shopping, serp.blog_posts AS ranking_blog_posts, serp.micro_format AS ranking_micro_format, serp.page AS ranking_page,
FALSE AS top10
FROM 
  FLATTEN([rankings.fortheloveofeverythingholy], serp) 
  AS details
JOIN ( 
  SELECT _id, MIN(serp.rank) WITHIN RECORD as minrank, 
  FROM [rankings.fortheloveofeverythingholy]
    WHERE serp.url LIKE '%moz.com%'
    OR serp.url LIKE '%www.seo.com%'
) 
AS top_ranks
ON top_ranks._id = details._id
  AND top_ranks.minrank = serp.rank
),
(
SELECT keyword AS top_keyword, rank_date AS top_rank_date, engine AS top_engine, locale AS top_locale, geo AS top_geo, mobile AS top_mobile, micro_format AS top_micro_format, news AS top_news, video AS top_video, integrated_places AS top_integrated_places, maps AS top_maps, image AS top_image, blog_posts AS top_blog_posts, shopping AS top_shopping, 
serp.rank AS top_rank, serp.url AS top_ranking_url, serp.title AS top_ranking_title, serp.indented AS top_ranking_indented, serp.authorship AS top_ranking_authorship, serp.image AS top_ranking_image, serp.news AS top_ranking_news, serp.maps AS top_ranking_maps, serp.video AS top_ranking_video, serp.shopping AS top_ranking_shopping, serp.blog_posts AS top_ranking_blog_posts, serp.micro_format AS top_ranking_micro_format, serp.page AS top_ranking_page,
TRUE AS top_top10
  FROM FLATTEN([rankings.fortheloveofeverythingholy], serp)
    WHERE serp.rank < 11
    AND (serp.url NOT LIKE '%moz.com%'
      OR serp.url NOT LIKE '%www.seo.com%')
);