Calculate if there are any intermittent test failures

test_failures.ex

def intermittent_failures(
      %{id: suite_id},
      spec \\ %{10 => 100, 9 => 80, 8 => 60, 7 => 45, 6 => 32, 5 => 25, 4 => 16, 3 => 9}
    ) do
  limit = spec |> Map.values() |> Enum.max()

  last_100_runs =
    from(r in Run,
      where: r.suite_id == ^suite_id,
      order_by: [desc: r.inserted_at],
      limit: ^limit,
      select: r.id
    )

  subquery =
    from(t in Test,
      join: r in subquery(last_100_runs),
      on: t.run_id == r.id,
      select: %{
        name: t.name,
        row_number: over(row_number(), partition_by: t.name, order_by: [desc: t.inserted_at]),
        cumulative_failures:
          over(
            sum(fragment("CASE WHEN t0.state = 'failed' THEN 1 ELSE 0 END")),
            partition_by: t.name,
            order_by: [desc: t.inserted_at]
          )
      }
    )

  starting_query =
    from(q in subquery(subquery),
      select: {q.name, min(q.cumulative_failures), min(q.row_number)},
      group_by: q.name,
      order_by: min(q.row_number)
    )

  spec
  |> Enum.reduce(starting_query, fn {failures, row_number}, query ->
    from(q in query,
      or_where: q.cumulative_failures == ^failures and q.row_number == ^row_number
    )
  end)
  |> Repo.all()
end