Tricky Rails ActiveRecord (SQL is acceptable!) query to find a proportion of users given conditions in two join tables

Let's see, maybe I can at least get you a SQL query, then we can worry about ActiveRecord-izing it:

SELECT resources.*, sum(case views.vote when 'up' 1 else 0 end) as up_votes, count(views.id) as total_views       FROM resources   JOIN views ON views.resource_id = resources.id  JOIN rates ON rates.resource_id = resources.id     AND rates.user_id = views.user_id  WHERE views.updated_at - views.created_at >= interval '5 minutes'  AND views.vote IS NOT NULL  GROUP BY resources.id

Now, I'm not completely sure this is right, of course, but it might be a start. You could then break it down into ActiveRecord form thus:

@resources = Resources.  select("resources.*, sum(case views.vote when 'up' 1 else 0 end) as up_votes, count(views.id) as total_views").  joins("JOIN views ON views.resource_id = resources.id JOIN rates ON rates.resource_id = resources.id AND rates.user_id = views.user_id").  where("views.updated_at - views.created_at >= interval '5 minutes' AND views.vote IS NOT NULL").  group("GROUP BY resources.id")

The up_votes and total_views would be attached to each Resource object, so you could call and manipulate them (I've found they get cast as strings for some reason, so you may need to use .to_f on 'em:

ratios = @resources.map{|r| r.up_votes.to_f/r.total_views.to_f}

You could also do the division in the select, if you wanted the database to sort by the ratios or some such.

If you actually just wanted the ratio over all resources, just take out the grouping (although I don't know that ActiveRecord would be involved there, except insofar as it's providing the connection to the DB).