From Ecto v3.8.3 (literally the last release at the time of writing), you can have subqueries anywhere in where. The only trick is to use parent_as to access the parent user:
matching_parent_user =
from u in User,
where: u.email == parent_as(:user).email and u.status_id == 3 and parent_as(:user).status_id == 3,
select: count()
query =
from User,
as: :user,
where: subquery(matching_parent_user) > 1,
order_by: :email
If this doesn’t work, then a fragment should:
query =
from u in User,
where: fragment("(select count(*) from users inr where inr.email = ? and inr.status_id = 3 and ? = 3)", u.email, u.status_id) > 1,
order_by: :email