title: max and count on joing n to n tables
body: <p>I'm blocked with a query and I'm needing some help.
  If someone could help me I'd appreciate a lot :)</p>
  
  <p>I have two tables (I'm using only one movie for showing the situation):</p>
  
  <p><strong>Table Consumption</strong></p>
  
  <pre><code>client_id   movie_id    name        date_consumption
  XXX         1           MovieA          01/Jan/2000
  YYY         1           MovieA          01/Jan/2000
  ZZZ         1           MovieA          02/Jan/2000
  XXX         1           MovieA          02/Jan/2000
  ZZZ         1           MovieA          10/Jan/2000
  </code></pre>
  
  <p><strong>Table movies_owners</strong></p>
  
  <pre><code>movie_id    rightowner      date_buyed*
  A           LucasFilm       01/Jan/2000
  A           Disney          02/Jan/2000
  A           Sony            05/Jan/2000
  </code></pre>
  
  <p>**Date_buyed : It's the date where the movie belongs to a new right owner.*</p>
  
  <p><strong>The ideia is simple</strong>: </p>
  
  <blockquote>
    <p>I have to find the count of clients who watched a movie for day with
    the correct Right Owner in the day this movie was watched.</p>
  </blockquote>
  
  <p><strong>Table Expected</strong></p>
  
  <pre><code>movie_id    date_consumption    rightowner      consumption(count)
  MovieA      01/Jan/2000         LucasFilm       2
  MovieA          02/Jan/2000         Disney          2
  MovieA          10/Jan/2000         Sony            1
  </code></pre>
  
  <p>-- </p>
  
  <p>With this query I can find the correct right owner of the movie in some day (max of all buyed dates before the day in question):</p>
  
  <pre><code>SELECT A.movie_id, A.date_buyed, A.rightowner
      FROM movies_owners A
      WHERE A.date_buyed EXISTS (
          SELECT max(date_buyed) 
          FROM movies_owners
          WHERE TO_DATE(date_buyed) &lt;= TO_DATE('2000-01-02') AND movie_id = 'MovieA') 
      AND movie_id = 'MovieA';
  </code></pre>
  
  <p>But my problem is when joing with the consumption table.
  I can't use the date_consumption from table consumption in a sub query.</p>
  
  <p>I tried to break into a auxiliar table for doing the join, but I still can't find the result. =\</p>
  
  <p>Can someone has, at least, an ideia or suggestion for me please?
  Thank you all in advanced.</p>
  
  <p><em>Juste for info: I'm working with Hive, but the sintax is almost the same from Sql.</em></p>