I want to combine two queries into 1 query to optimize my code. I tried my own by getting error.
Also, In doc, it is given that Subqueries are currently only supported in the from and join fields.. So, does following can be done or not. And, if not then how could I optimized them.

Check my implementation-

1st Query

    query = from(pipeline in CandidatePipeline,
        join: ap in assoc(pipeline, :applicant),
        where:
          pipeline.job_opening_id == ^job_id and pipeline.job_stage_id == ^stage_id and
            pipeline.status == ^status,
        preload: [applicant: ap],
        join: a in AppliedOpening,
        on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
        join: applicant in Applicant,
        on: applicant.id == pipeline.applicant_id,
        join: r in Result,
        on: r.applied_opening_id == a.id,
        where: r.state == ^result,
        order_by: [desc_nulls_last: r.percent, asc: applicant.name]
      )

2nd Query

    query = from(pipeline in CandidatePipeline,
        join: ap in assoc(pipeline, :applicant),
        where:
          pipeline.job_opening_id == ^job_id and pipeline.job_stage_id == ^stage_id and
            pipeline.status == ^status,
        preload: [applicant: ap],
        join: a in AppliedOpening,
        on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
        where: a.state == ^applied_state,
        join: applicant in Applicant,
        on: applicant.id == pipeline.applicant_id,
        order_by: [asc: applicant.name]
      )

Query that I tried, but not working

      sub_query =  from pipeline in CandidatePipeline,
      join: ap in assoc(pipeline, :applicant),
      where:
        pipeline.job_opening_id == ^job_id and pipeline.job_stage_id == ^stage_id and
          pipeline.status == ^status,
      preload: [applicant: ap]

      cond do
        result == "unattempted" || result == "skipped" ->
          query = Ecto.subquery(sub_query),
          join: a in AppliedOpening,
          on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
          where: a.state == ^applied_state,
          join: applicant in Applicant,
          on: applicant.id == pipeline.applicant_id,
          order_by: [asc: applicant.name]
        true ->
          query = Ecto.subquery(sub_query),
          join: a in AppliedOpening,
          on: a.job_opening_id == pipeline.job_opening_id and a.applicant_id == pipeline.applicant_id,
          join: applicant in Applicant,
          on: applicant.id == pipeline.applicant_id,
          join: r in Result,
          on: r.applied_opening_id == a.id,
          where: r.state == ^result,
          order_by: [desc_nulls_last: r.percent, asc: applicant.name]
      end

In my just above code, getting error on 2nd line just after cond do that is- syntax error before: ','
Your help will be appreciated. Also, if you know more better or optimized way let me know.