title: How do I write a proper MySQL sub-query in Rails with ActiveRecord?
body: <p>I'm not sure if my problem is that I'm writing the MySQL wrong or if I'm mis-using subqueries with ActiveRecord. 
  <br><br>
  Here's the background:<br>
  History is the table. History has 'id', 'user_id', 'state', and 'created_at'. A user may have the same state more than once in history. 
  <br><br>Here's what I'm trying to pull:
  <br>
  For a given day, find the users who unsubscribed for the first time. 
  <br><br>
  Here's the inaccurate query I originally wrote:</p>
  
  <pre><code>History.select('distinct user_id').where(state: 'unsubscribed').where('date(created_at) = ?', Date.today).all
  </code></pre>
  
  <p>That gives me the number of people who unsubscribed today, but not the number of people who unsubscribed for the first time today. If someone unsubscribed 30 days ago and unsubscribed again today, they would be in this set of results.
  <br><br>
  I know how to find the pull the result set using Ruby by doing two queries and some Array subtractions. My question is, how do I pull these results with 1 query / subquery. Here was my attempt that didn't work:</p>
  
  <pre><code>sub_query = "(SELECT MIN(h.created_at) FROM history AS h WHERE h.state = 'unsubscribed' AND h.user_id = history.user_id)"
  History.where('date(created_at) = ?', Date.today).where("history.user_id IN (#{sub_query})").all
  </code></pre>
  
  <p><br>
  Is that even close to right approach? Should I be doing a sub-select instead? Should I just write the query in pure SQL and then use ActiveRecord::Base.connection.execute()? Any help would be helpful and much appreciated. </p>