title: Get count(table1_id) based on max(table2_date) that is equal to given date. But each table1_id has many table2_dates
body: <p>I have two tables with the following data</p>
  
  <p>Table 1: customers</p>
  
  <pre><code>|---|-----|
  |id |name |
  |---|-----|
  |1  |Bob  |
  |---|-----|
  |2  |Alice|
  |---|-----|
  </code></pre>
  
  <p>Table 2: subscriptions</p>
  
  <pre><code>|---|------------|-------------|
  |id |customer_id |end_date     |
  |---|------------|-------------|
  |1  |1           |2020-03-16   |
  |---|------------|-------------|
  |2  |1           |2020-04-05   |
  |---|------------|-------------|
  |3  |2           |2020-02-05   |
  |---|------------|-------------|
  |4  |1           |2020-05-04   |
  |---|------------|-------------|
  |5  |2           |2020-03-16   |
  |---|------------|-------------|
  </code></pre>
  
  <p>I need the count of customers where max(subscriptions.end_date) of each customer is equal to today. </p>
  
  <p>My current query</p>
  
  <pre class="lang-sql prettyprint-override"><code>select COUNT(customers.id), MAX(customer_payments.package_end_date) 
  from `customers` 
  inner join `customer_payments` on `customer_payments`.`customer_id` = `customers`.`id` 
  group by `customer_payments`.`package_end_date` 
  having DATE(customer_payments.package_end_date) = '2020-03-16'
  </code></pre>
  
  <p>It is returning the customers who have end_date as today also; but not of the greatest date. In the given data, if I run the query for today's date, I am getting the count 2. It is selecting both customer_id 1 and 2 even though the customer_id of 1 has the greatest date than today.</p>
  
  <p>I have also tried queries mentioned in <a href="https://stackoverflow.com/questions/34651063/find-count-of-rows-in-first-table-that-match-ids-of-row-of-second-table-with-max">these answers</a> also getting the same output.  </p>