ActiveRecord Subquery Inner Join

Disclamer: I've never used PostGIS

First in your final request, it seems like you've missed the WHERE "users"."active" = 1; part.

Here is what I'd do:

First add a active scope on user (for reusability)

scope :active, -> { User.where(active: 1) }

Then for the actual query, You can have the sub query without executing it and use it in a joins on the User model, such as:

subquery = ZipCache.select(:centroid).where(postalcode: '<postalcode>')
User.active
    .joins("INNER JOIN (#{subquery.to_sql}) sub ON ST_Intersects(users.vendor_coverage, sub.centroid)")
    .count

This allow minimal raw SQL, while keeping only one query.

In any case, check the actual sql request in your console/log by setting the logger level to debug.

The amazing tool scuttle.io is perfect for converting these sorts of queries:

User.select(Arel.star.count).where(User.arel_table[:active].eq(1)).joins(
  User.arel_table.join(ZipCach.arel_table).on(
    Arel::Nodes::NamedFunction.new(
      'ST_Intersects', [
        User.arel_table[:vendor_coverage], Sub.arel_table[:centroid]
      ]
    )
  ).join_sources
)