title: How to filter a table by a second table attribute on SQL
body: <p>I have a table named <code>lines</code>, that has registered every transaction made in every <code>account</code> with it's <code>datetime</code>. Along with the amount of the transaction the table has the <code>balance</code> for that account at that given time. The line registers the date with the hour (<code>datetime</code>) when the transaction was made and registers the datetime when the line was created (<code>created_at</code>).</p>
  <p>So the lines table looks like this:</p>
  <pre><code>id | account_id | amount | balance | datetime                   | created_at
   1 | ac1        | 5      | 13      | 2020-03-24 08:57:31.195993 | 2020-03-24 08:57:31.195993
   2 | ac1        | 3      | 10      | 2020-03-24 08:57:31.195993 | 2020-03-24 08:57:30.195993
   3 | ac1        | 5      | 5       | 2020-03-24 06:54:31.195993 | 2020-03-24 06:57:31.195993
   4 | ac2        | 6      | 11      | 2020-03-23 08:57:31.195993 | 2020-03-23 08:57:31.195993
   4 | ac2        | 0      | 5       | 2020-03-23 07:57:31.195993 | 2020-03-23 07:57:31.195993
   5 | ac3        | 6      | 13      | 2020-03-24 08:57:31.195993 | 2020-03-24 08:57:31.195993
  </code></pre>
  <p>I would like to get the balance of the last transaction of each day for every account.
  It's important to mention that sometimes there are two <code>lines</code> that have the same <code>datetime</code> for an <code>account</code>, in that case I want the line with higher <code>created_at</code> datetime.
  So I would like my output to be:</p>
  <pre><code>id | account_id | amount | balance | date       | datetime
   1 | ac1        | 5      | 13      | 2020-03-24 | 2020-03-24 08:57:31.195993
   4 | ac2        | 6      | 11      | 2020-03-23 | 2020-03-23 08:57:31.195993
   5 | ac3        | 6      | 13      | 2020-03-24 | 2020-03-24 08:57:31.195993
  </code></pre>
  <p>I've managed to do this with lots of <code>INNER JOINS</code> but the query is just too slow.</p>
  <pre><code>SELECT DATE(lines.datetime) as date, lines.account_id as account, lines.balance as balance
  FROM lines
  INNER JOIN (
    SELECT DATE(a.datetime) date, a.account_id, MAX(a.created_at) created_at
    FROM lines a
    INNER JOIN (
      SELECT b.account_id, DATE(b.datetime) date, MAX(b.datetime) datetime
      FROM lines b
      GROUP BY b.account_id, DATE(b.date)
    ) b ON a.account_id = b.account_id AND a.datetime = b.datetime
    GROUP BY DATE(a.datetime), a.account_id
  ) c ON lines.account_id = c.account_id AND DATE(lines.datetime) = c.date AND lines.created_at = c.created_at
  </code></pre>
  <p>I know there must be a better way, just couldn't find it by myself :(</p>