title: Need SQL query to find users with activity in two different periods for each day
body: <p>I'm trying to do some analysis on usage of our web based app.
  I have a table with the the following columns
      email address
      activity date</p>
  
  <p>I want to create a query that answers this question:
  For each day in the past 180 days, how many people who did an activity between 60 and 30 days prior ALSO did an activity between 30 and 0 days prior.</p>
  
  <p>I already have this working as a stored procedure where I literally loop over the past 180 days (using a date table with 1 row per day), but this is kinda slow as I'm doing 180 queries.</p>
  
  <p>I also tried my hand at doing it with one query with the IN clause but it took about 5 minutes to complete (the table only has about 2,000 rows total so I'm guessing it was HIGHLY un-optimized)</p>
  
  <p>How would I do this with one query (or even a stored proc) that's optimized?</p>
  
  <p>Here is the current stored proc (which works but is slow) if it helps:</p>
  
  <pre><code>BEGIN
      DECLARE mydate DATE;
      DECLARE period1 INT;
      DECLARE period2 INT;
      DECLARE done INT;
  
      DECLARE cur CURSOR FOR SELECT date_value from dim_date  order by date_value DESC;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
      SET done = 0;
      OPEN cur;
  
      REPEAT
  
      FETCH cur INTO mydate;
      IF NOT done THEN
    REPLACE INTO churn (payment_received,period2,period1,churn_name)
  
      select
  mydate, 
  count(distinct(case when (sales.payment_received BETWEEN DATE_SUB(mydate,INTERVAL p2 month) AND DATE_SUB(mydate,INTERVAL p1 month)) then email end)) AS period2,
  (
  select count(distinct(case when (sales.payment_received BETWEEN DATE_SUB(mydate,INTERVAL p1 month) AND mydate) then email end))
  from sales where subscription = 1 AND email in (select email from sales where sales.payment_received BETWEEN DATE_SUB(mydate,INTERVAL p2 month) AND DATE_SUB(mydate,INTERVAL p1 month) ) 
  ) 
  AS period1,
  churn_name as cname
  from sales 
  where subscription = 1;
  
      END IF;    
      UNTIL done END REPEAT;
      CLOSE cur;
  
  END;;
  </code></pre>
  
  <p>Thanks!</p>