Sub-selects in ecto queries

You can just use JOINs. I am on a mobile right now, so I will not rewrite that query for you, but I believe that you can do it on your own.

Yes, I used JOINs with subqueries, but as I need two aggregate parameters, and they both use the same tables, my JOINs somehow mix between each other and give wrong numbers.
Here is my approach:

    orders_query =
      from(ord in Order,
        select: [:id, :company_id],
        left_join: s in assoc(ord, :shipments),
        where: not ord.archived and is_nil(s.id)
      )

    shipments_query =
      from(sh in Shipment,
        left_join: o2 in assoc(sh, :order),
        where: is_nil(sh.batch_id),
        select: %{id: sh.id, company_id: o2.company_id}
      )

    Company
    |> filter_by_user(user)
    |> only_active(true)
    |> join(:left, [c], o in subquery(orders_query), on: o.company_id == c.id, as: :orders)
    |> join(:left, [c], s in subquery(shipments_query), on: s.company_id == c.id, as: :shipments)
    |> group_by([c], c.id)
    |> select_merge([c, o, s], %{
      orders_count: count(o.id),
      shipments_count: count(s.id)
    })
    |> Repo.all(prefix: tenant)

What you probably want is something like:

SELECT
  c.*,
  orders.count,
  s.count
FROM companies c
INNER JOIN (SELECT o.company_id company_id, COUNT(*) count
            FROM orders o
            LEFT OUTER JOIN shipments s ON s.order_id = o.id
            WHERE s.id IS NULL AND NOT o.archived
            GROUP BY o.company_id) orders
    ON orders.company_id = c.id
-- and so on

So in Elixir it would be like:

orders_query =
  from o in Order,
    left_outer_join: s in assoc(o, :shipments),
    where: not o.archived and is_nil(s.id),
    group_by: o.company_id,
    select: %{id: o.company_id, count: count()}

shipments_query =
  from s in Shipment,
    left_inner_join: o in assoc(sh, :order),
    where: is_nil(s.batch_id),
    select: %{id: o.company_id, count: count()}

from c in Company,
  left_inner_join: o in subquery(orders_query), on: o.id == c.id,
  left_inner_join: s in subquery(shipments_query), on: s.id == c.id,
  select_merge: %{orders_count: o.count, shipments_count: s.count}

With minor modifications it worked, thanks!
Had to add group_by and coalesce to deal with nil count values.
Here is the final working query:

   orders_query =
      from(o in Order,
        left_join: s in assoc(o, :shipments),
        where: not o.archived and is_nil(s.id),
        group_by: o.company_id,
        select: %{company_id: o.company_id, count: count()}
      )

    shipments_query =
      from(s in Shipment,
        inner_join: o in assoc(s, :order),
        where: is_nil(s.batch_id),
        group_by: o.company_id,
        select: %{company_id: o.company_id, count: count()}
      )

    Company
    |> filter_by_user(user)
    |> only_active(true)
    |> join(:left, [c], o in subquery(orders_query), on: o.company_id == c.id)
    |> join(:left, [c], s in subquery(shipments_query), on: s.company_id == c.id)
    |> group_by([c, o, s], [c.id, o.count, s.count])
    |> select_merge([c, o, s], %{
      orders_count: coalesce(o.count, 0),
      shipments_count: coalesce(s.count, 0)
    })
    |> Repo.all(prefix: tenant)