Create query to get count of uncompleted calls

Question

There is table waiter_log as

+---------+----------------+--------------+--------------+
| call_id | queue_num_curr | ast_num_curr | proceed_wait |
+---------+----------------+--------------+--------------+
| f27de4f | 9010           | 2            |            1 |
| f27de4f | 9002           | 5            |            1 |
| f27de4f | 9003           | 1            |            0 | 
| asdf231 | 9010           | 2            |            1 |
| asdf231 | 9002           | 5            |            1 |
| rete125 | 9010           | 2            |            1 |
| rete125 | 9009           | 5            |            1 |
| a7rf5gs | 9003           | 2            |            1 |
| a7rf5gs | 9006           | 5            |            1 |
| a7rf5gs | 9009           | 1            |            0 |
| qawe234 | 9003           | 2            |            1 |
| qawe234 | 9008           | 5            |            1 |
| qawe234 | 9004           | 1            |            0 |
| 49c43ad | 9004           | 2            |            1 |
| 49c43ad | 9007           | 5            |            1 |
+---------+----------------+--------------+--------------+

Call with call-id 'f27de4f' started in 9010 and finished in 9003 because there is a record with proceed_wait = 0 for call-id='f27de4f'
Call with call-id 'asdf231' started in 9010, still proceed in 9002 and not finished yet because there is no record with proceed_wait = 0 for call-id='asdf231' Similarly for call with call-id 'rete125' there is no record with proceed_wait = 0 and this call is not completed too. So,for queue 9010 query result should be 2 (count of uncompleted calls) For 9003 result should be 0 , because all calls for 9003 ('a7rf5gs' and 'qawe234') are completed. For 9004 result should be 1 because there is no record with proceed_wait = 0 for call with call-id '49c43ad'.

How to create a query to get count on uncompleted calls as:

queue_num   count 
9010         2
9004         1

UPD: Here i updated my question Create query to get count of uncompleted calls group by 2 fields

Answer 1

Here's another method that works without correlated subqueries or window functions:

For each row w1, try to find another row w2 with the same call_id and a 0 indicating the call is complete. Using a LEFT OUTER JOIN, we can test for cases where no w2 row exists for a given call_id.

Then do another join to a hypothetical row w3 with the same call_id and a lesser ast_num_curr value. Again, using outer join, we can check that no such row exists. This means w1 must have the least value for ast num for that call_id.

SELECT w1.call_id, w1.queue_num_curr
FROM waiter_log AS w1
LEFT OUTER JOIN waiter_log AS w2
  ON w1.call_id = w2.call_id AND w2.proceed_wait = 0 
LEFT OUTER JOIN waiter_log AS w3
  ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr
WHERE w2.call_id IS NULL AND w3.call_id IS NULL;

Output:

+---------+----------------+
| call_id | queue_num_curr |
+---------+----------------+
| 49c43ad |           9004 |
| asdf231 |           9010 |
| rete125 |           9010 |
+---------+----------------+

To get the counts per queue_num_curr, wrap the query above in a derived-table subquery, and do the count in the outer query:

SELECT queue_num_curr, COUNT(*) AS count
FROM (
  SELECT w1.call_id, w1.queue_num_curr
  FROM waiter_log AS w1
  LEFT OUTER JOIN waiter_log AS w2
    ON w1.call_id = w2.call_id AND w2.proceed_wait = 0
  LEFT OUTER JOIN waiter_log AS w3
    ON w1.call_id = w3.call_id AND w1.ast_num_curr > w3.ast_num_curr
  WHERE w2.call_id IS NULL AND w3.call_id IS NULL
) AS t
GROUP BY queue_num_curr;

Output:

+----------------+-------+
| queue_num_curr | count |
+----------------+-------+
|           9004 |     1 |
|           9010 |     2 |
+----------------+-------+