title: Getting difference of a column from same table
body: <p>I have a table with data that is similar to this table below</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>session_id</th>
  <th>user_id</th>
  <th>action</th>
  <th>log_time</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>3</td>
  <td>join</td>
  <td>1642645048</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>3</td>
  <td>left</td>
  <td>1642645048</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>3</td>
  <td>join</td>
  <td>1642645552</td>
  </tr>
  <tr>
  <td>4</td>
  <td>1</td>
  <td>3</td>
  <td>left</td>
  <td>1642646072</td>
  </tr>
  <tr>
  <td>5</td>
  <td>1</td>
  <td>3</td>
  <td>join</td>
  <td>1642646632</td>
  </tr>
  <tr>
  <td>6</td>
  <td>1</td>
  <td>3</td>
  <td>left</td>
  <td>1642646736</td>
  </tr>
  <tr>
  <td>7</td>
  <td>1</td>
  <td>5</td>
  <td>join</td>
  <td>1642647083</td>
  </tr>
  <tr>
  <td>8</td>
  <td>1</td>
  <td>5</td>
  <td>join</td>
  <td>1642649879</td>
  </tr>
  <tr>
  <td>9</td>
  <td>1</td>
  <td>5</td>
  <td>left</td>
  <td>1642649951</td>
  </tr>
  <tr>
  <td>10</td>
  <td>1</td>
  <td>5</td>
  <td>join</td>
  <td>1642650112</td>
  </tr>
  <tr>
  <td>11</td>
  <td>1</td>
  <td>5</td>
  <td>join</td>
  <td>1642650159</td>
  </tr>
  <tr>
  <td>12</td>
  <td>1</td>
  <td>5</td>
  <td>join</td>
  <td>1642651005</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>log_time is saved as a unix time</p>
  <p><strong>Question:</strong> Is it possible to retrieve the total amount of time that a user was in a session?</p>
  <p>So it would do something like <code>total_participation = (&quot;1st left&quot; - &quot;1st join&quot;) + (&quot;2nd left&quot; - &quot;2nd join&quot;) + (&quot;3rd left - &quot;3rd join&quot;)</code></p>
  <p>I've already got the difference between <strong>first join</strong> and <strong>last left</strong> time by doing the following query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT s1.session_id as 'Id',
         u.name AS 'Participant',
         IFNULL(TIME_FORMAT(SEC_TO_TIME(s2.time_log - s1.time_log), '%Hh %im %ss'), 0) AS 'TotalParticipation'
  FROM tblSessionLog AS s1
  LEFT JOIN tblSessionLog AS s2 ON (
      s2.id = (
          SELECT id
          FROM tblSessionLog
          WHERE action = 'left'
              AND user_id = s1.user_id
              AND id &gt; s1.id
          ORDER BY time_log DESC
          LIMIT 1
      )
  )
  LEFT JOIN tblUser AS u ON u.id = s1.user_id -- used only to get participant name
  WHERE s1.action = 'join'
  GROUP BY s1.session_id, s1.user_id
  ORDER BY s1.session_id, s1.user_id;
  </code></pre>
  <p>But I can't seem to get how to remove the time in between the participant have left and join backed again. Or is that not possible on SQL and should be handled on backend code?</p>
  <p>Not exactly a copy but more or less, the actual data is like this sample SQL Fiddle here: <a href="http://sqlfiddle.com/#!9/2d8f6c/1/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/2d8f6c/1/0</a></p>
  <p><strong>Update:</strong></p>
  <p>@Akina's <a href="https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=2f59aef6c450e23cb218a8fd016201f0" rel="nofollow noreferrer"><strong>solution</strong></a> work well when the data is consistent which is very much appreciated but I found out it will not be suitable on my case as there are instances where it is possible to have more than one <code>join</code> action before having <code>left</code> action, as well as no <code>left</code> action after <code>join</code> action. I updated the example table above to further show the actual data. Updated the sample fiddle as well.</p>
  <p>Any leads will be really appreciated. And apologies as for some reason I need to do this on MySQL without the help of backend code. Thanks!</p>