Untitled

      SELECT
        explored_finding_code,
        explored_finding_answer_rank,
        explored_weighted_share,
        joint_finding_type,
        joint_answer_group_code,
        top_answer_group_weighted_shares,
        bottom_answer_group_weighted_shares,
        top_answer_group_weighted_shares[1] AS top_1_answer_group_weighted_share,
        CASE
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count IS NULL
            THEN 'only finding'
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count = 1
            THEN 'finding top, 1 bottom'
          WHEN top_answer_group_finding_count = 1 AND bottom_answer_group_finding_count IS NULL
            THEN 'finding bottom, 1 top'
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count = 2
            THEN 'finding top, 2 bottom'
           WHEN top_answer_group_finding_count = 2 AND bottom_answer_group_finding_count IS NULL
            THEN 'finding bottom, 2 top'
          WHEN top_answer_group_finding_count = 1 AND bottom_answer_group_finding_count = 1
            THEN 'finding middle, 1 top, 1 bottom'
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count = 3
            THEN 'finding top, 3 bottom'
          WHEN top_answer_group_finding_count = 3 AND bottom_answer_group_finding_count IS NULL
            THEN 'finding bottom, 3 top'
          WHEN top_answer_group_finding_count = 1 AND bottom_answer_group_finding_count = 2
            THEN 'finding middle, 1 top, 2 bottom'
          WHEN top_answer_group_finding_count = 2 AND bottom_answer_group_finding_count = 1
            THEN 'finding middle, 2 top, 1 bottom'
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count = 4
            THEN 'finding top, 4 bottom'
          WHEN top_answer_group_finding_count = 4 AND bottom_answer_group_finding_count IS NULL
            THEN 'finding bottom, 4 top'
          WHEN top_answer_group_finding_count = 1 AND bottom_answer_group_finding_count = 3
            THEN 'finding middle, 1 top, 3 bottom'
          WHEN top_answer_group_finding_count = 3 AND bottom_answer_group_finding_count = 1
            THEN 'finding middle, 3 top, 1 bottom'
          WHEN top_answer_group_finding_count = 2 AND bottom_answer_group_finding_count = 2
            THEN 'finding middle, 2 top, 2 bottom'
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count = 4
            THEN 'finding top, 4 bottom'
          WHEN top_answer_group_finding_count = 4 AND bottom_answer_group_finding_count IS NULL
            THEN 'finding bottom, 4 top'
           WHEN top_answer_group_finding_count = 1 AND bottom_answer_group_finding_count = 3
            THEN 'finding middle, 1 top, 3 bottom'
          WHEN top_answer_group_finding_count = 3 AND bottom_answer_group_finding_count = 1
            THEN 'finding middle, 3 top, 1 bottom'
          WHEN top_answer_group_finding_count IS NULL AND bottom_answer_group_finding_count > 4
            THEN 'finding top, > 4 bottom'
          WHEN top_answer_group_finding_count > 4 AND bottom_answer_group_finding_count IS NULL
            THEN 'finding bottom, > 4 top'
          WHEN top_answer_group_finding_count = 1 AND bottom_answer_group_finding_count > 3
            THEN 'finding middle, 1 top, > 3 bottom'
          WHEN top_answer_group_finding_count > 3 AND bottom_answer_group_finding_count = 1
            THEN 'finding middle, > 3 top, 1 bottom'
          WHEN top_answer_group_finding_count >= 2 AND bottom_answer_group_finding_count >= 2
            THEN 'finding middle, > 2 top, > 2 bottom'
          END AS joint_answer_group_status
      FROM TopBottomCounts