title: SQL query to calculate visit duration from log table
body: <p>I have a MySQL table LOGIN_LOG with fields ID, PLAYER, TIMESTAMP and ACTION. ACTION can be either 'login' or 'logout'. Only around 20% of the logins have an accompanying logout row. For those that do, I want to calculate the average duration.</p>
  
  <p>I'm thinking of something like</p>
  
  <pre><code>select avg(LL2.TIMESTAMP - LL1.TIMESTAMP)
  from LOGIN_LOG LL1
  inner join LOGIN_LOG LL2 on LL1.PLAYER = LL2.PLAYER and LL2.TIMESTAMP &gt; LL1.TIMESTAMP
  left join LOGIN_LOG LL3 on LL3.PLAYER = LL1.PLAYER
    and LL3.TIMESTAMP between LL1.TIMESTAMP + 1 and LL2.TIMESTAMP - 1
    and LL3.ACTION = 'login'
  where LL1.ACTION = 'login' and LL2.ACTION = 'logout' and isnull(LL3.ID)
  </code></pre>
  
  <p>is this the best way to do it, or is there one more efficient?</p>