title: Best way to Join on Date Range PostgreSQL
body: <p>This Periscope Data Blog posts describes how to use the SQL query below to find the number of users who visited your website this month who also came last month, i.e. "retention": </p>
  
  <pre><code>with monthly_activity as (
    select distinct
      date_trunc('month', created_at) as month,
      user_id
    from events
  )
  select
    this_month.month,
    count(distinct user_id)
  from monthly_activity this_month
  join monthly_activity last_month
    on this_month.user_id = last_month.user_id
    and this_month.month = last_month.month + interval '1 month' -- date join
  group by month
  </code></pre>
  
  <p>But what if retention is defined as not "visited last month" but "visited <em>in any month in the past</em>"?</p>
  
  <p>In that case, we would want the date join (highlighted as a comment in the above query) for not just 1 month in the past but <em>all</em> months in the past. </p>
  
  <p>I.e. something like this, but without hardcoding:</p>
  
  <pre><code>  and this_month.month = last_month.month + interval '1 month' 
    and this_month.month = last_month.month + interval '2 month' 
    and this_month.month = last_month.month + interval '3 month' 
    ...
  </code></pre>
  
  <p>I'm stumped on what's the best way to do this. </p>
  
  <p>I've tried the following workarounds, although I think they lead to weird cartesian products since my queries aren't running:</p>
  
  <pre><code>AND current_month.month &gt; past_logs.month -- doesn't run
  </code></pre>
  
  <p>and </p>
  
  <pre><code>AND past_logs.month IN (select i::date from generate_series('2014-10-01', 
                          current_month.month, '1 month'::interval) i) -- doesn't run
  </code></pre>
  
  <p>Thoughts on best way to do this?</p>