title: Elasticsearch SQL like subquery aggregation
body: <p>I am playing around with ES to understand if it can cover most of my scenarios.
  I am at the point where I am stuck thinking how to reach a certain results that is pretty simple in SQL.</p>
  
  <p>This is the example</p>
  
  <p>In elastic I have an index with this documents</p>
  
  <pre><code>{ "Id": 1,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160101,  "BestBeforeDate": 20160102, "BiteBy":"John"}
  { "Id": 2,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160102,  "BestBeforeDate": 20160104, "BiteBy":"Mat"}
  { "Id": 3,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160103,  "BestBeforeDate": 20160105, "BiteBy":"Mark"}
  { "Id": 4,  "Fruit": "Banana", "BoughtInStore"="Jungle", "BoughtDate"=20160104,  "BestBeforeDate": 20160201, "BiteBy":"Simon"}
  { "Id": 5,  "Fruit": "Orange", "BoughtInStore"="Jungle", "BoughtDate"=20160112,  "BestBeforeDate": 20160112, "BiteBy":"John"}
  { "Id": 6,  "Fruit": "Orange", "BoughtInStore"="Jungle", "BoughtDate"=20160114,  "BestBeforeDate": 20160116, "BiteBy":"Mark"}
  { "Id": 7,  "Fruit": "Orange", "BoughtInStore"="Jungle", "BoughtDate"=20160120,  "BestBeforeDate": 20160121, "BiteBy":"Simon"}
  { "Id": 8,  "Fruit": "Kiwi", "BoughtInStore"="Shop", "BoughtDate"=20160121,  "BestBeforeDate": 20160121, "BiteBy":"Mark"}
  { "Id": 8,  "Fruit": "Kiwi", "BoughtInStore"="Jungle", "BoughtDate"=20160121,  "BestBeforeDate": 20160121, "BiteBy":"Simon"}
  </code></pre>
  
  <p>If I would like to know how many fruits bought in different store people bite in specific date range in SQL I write something like this</p>
  
  <pre><code>SELECT 
      COUNT(DISTINCT kpi.Fruit) as Fruits, 
      kpi.BoughtInStore,
      kpi.BiteBy 
  FROM 
      (
          SELECT f1.Fruit, f1.BoughtInStore, f1.BiteBy
          FROM FruitsTable f1
          WHERE f1.BoughtDate = (
              SELECT MAX(f2.BoughtDate)
              FROM FruitsTable f2
              WHERE f1.Fruit = f2.Fruit
              and f2.BoughtDate between 20160101 and 20160131
              and (f2.BestBeforeDate between 20160101 and 20160131)
          )
      ) kpi   
  GROUP BY kpi.BoughtInStore, kpi.ByteBy
  </code></pre>
  
  <p>the results is something like this</p>
  
  <pre><code>{ "Fruits": 1,  "BoughtInStore": "Jungle", "BiteBy"="Mark"}
  { "Fruits": 1,  "BoughtInStore": "Shop", "BiteBy"="Mark"}
  { "Fruits": 2,  "BoughtInStore": "Jungle", "BiteBy"="Simon"}
  </code></pre>
  
  <p>Do you have any idea how I can reach the same result in Elastic with aggregation?</p>
  
  <p>In a few words the problems I am facing in elastic are:</p>
  
  <ol>
  <li>How prepare a subsed of data before aggregation (like in this example the latest row in the range per each Fruit)</li>
  <li>How to group results by multiple fields</li>
  </ol>
  
  <p>Thank you</p>