title: Is there an better way to query whether a condition is fulfilled before a row within a time duration?
body: <p>I wrote a query to count the amount of item with some conditions. But the query looks complex and take a long time to run. Is there a better way to get the same result?</p>
  
  <p>My table looks like this. </p>
  
  <pre><code>timestamp      uid     action      item   state
  ------------------------------------------------
    2010          1      switch      null      on
    2100          1        move         A    null    
    2300          1        move         A    null
    2700          1        move         B    null
    2013          2      switch      null     off
    2213          2        move         C    null
    2513          2        move         A    null
    2200          3      switch      null     off
    2350          3        move         A    null
    2513          3      switch      null      on
    2700          3        move         A    null
  </code></pre>
  
  <p>Basically, I want to get the number of each item with a condition that state is on before and within a period of time. </p>
  
  <p>My query is</p>
  
  <pre><code>WITH action_move (
    SELECT timestamp, uid, item
    FROM table
    WHERE action=move AND item IS NOT NULL
  )
  SELECT item, count(*)
  FROM action_move
  WHERE EXISTS (
    SELECT timestamp
    FROM table
    WHERE 
      uid=action_move.uid
      action=switch 
      AND state=on 
      AND (action_move.timestamp - timestamp) &lt; 1000
  )
  GROUP BY item;
  </code></pre>
  
  <p>My result</p>
  
  <pre><code>item    count
  -------------
    A        3
    B        1
    C        0
  </code></pre>