title: SQL query to get customers who have purchased same product in two different quarters - Date Ranges
body: <p>I have a table <code>Customer_Order_Data</code> with these columns:</p>
  <ol>
  <li>customer_id</li>
  <li>order_id</li>
  <li>product_id</li>
  <li>quantity</li>
  <li>order_date</li>
  </ol>
  <p>I want to find the customers who have purchased the same product (<code>product_id</code>) in two different quarters - Q1 2020(Jan-Mar) and Q2(Apr-Jun). For both the quarters the quantity purchase should be greater than 5.
  I have an additional requirement to display the most frequently purchased quantity and most recently purchased quantity for the selected rows.</p>
  <p>Let's say I have the following data -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Order_id</th>
  <th>Customer_id</th>
  <th>product_id</th>
  <th>Quantity</th>
  <th>order_date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>00001</td>
  <td>ABCD</td>
  <td>B019</td>
  <td>7</td>
  <td>2020-01-01</td>
  </tr>
  <tr>
  <td>00002</td>
  <td>ABCD</td>
  <td>B019</td>
  <td>6</td>
  <td>2020-05-23</td>
  </tr>
  <tr>
  <td>00003</td>
  <td>EFGH</td>
  <td>B018</td>
  <td>8</td>
  <td>2020-01-12</td>
  </tr>
  <tr>
  <td>00004</td>
  <td>ABCD</td>
  <td>B019</td>
  <td>7</td>
  <td>2020-02-14</td>
  </tr>
  <tr>
  <td>00005</td>
  <td>EFGH</td>
  <td>B018</td>
  <td>6</td>
  <td>2020-04-18</td>
  </tr>
  <tr>
  <td>00006</td>
  <td>ABCD</td>
  <td>B019</td>
  <td>7</td>
  <td>2020-04-19</td>
  </tr>
  <tr>
  <td>00007</td>
  <td>EFGH</td>
  <td>B018</td>
  <td>8</td>
  <td>2020-03-12</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to filter out the customers who have purchased same product in both Q1 and Q2 where <code>quantity  &gt; 5</code>.
  I want to group the rows based on customer_id.</p>
  <p>Result expected -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Customer_id</th>
  <th>product_id</th>
  <th>Most frequently Purachsed</th>
  <th>Most Recently Purchased</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ABCD</td>
  <td>B019</td>
  <td>7</td>
  <td>6</td>
  </tr>
  <tr>
  <td>EFGH</td>
  <td>B018</td>
  <td>8</td>
  <td>6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Query I tried -</p>
  <pre><code>select t.*
  from mytable t
  where (
      select count(*) 
      from mytable t1 
      where 
          t1.customer_id = t.customer_id 
          and t1.product_id = t.product_id 
          and t1.quantity &gt; 5
          and t1.order_date BETWEEN '2020-01-01 00:00:00' AND '2020-03-31 23:59:59'
  ) &gt; 1
  and t.order_date BETWEEN '2020-04-01 00:00:00' AND '2020-06-30 23:59:59'
  and t.quantity &gt; 5;
  </code></pre>
  <p>This query is not correctly filtering out the customers who purchased same product in both the quarters.
  Also, I am not understanding how to add the dummy columns for Most Frequently purchased quantity and Most Recently purchased quantity for a product</p>