Retrieving records with the same ID where the number of occurrences of one value in a column is grea-CodePudding

Sorry if the title question is unclear, I'd imagine this has been asked before but I've searched for a while and I think I'm just not phrasing it particularly well.

I have a table of call records in which we record when the call is put on hold or picked up from hold. The callid column is how we refer to specific calls -- it's not the unique record id so there are multiple entries for the same callid when the call is on or off of hold. We record that information in the answered column which contains either a 0 for the call being on hold, or 1 for being picked up.

I'm trying to find situations where somebody was put on hold and then hangs up before the call is picked up again; that is, the last occurrence of that particular callid should have a 0 in the answered column. I started with this query:

SELECT * from table t1
WHERE NOT EXISTS(
    SELECT 1 from table t2
    WHERE t1.callid = t2.callid
    HAVING COUNT(CASE WHEN answered = 1 THEN 1 END)
)
order by id desc

However, this doesn't cover situations where the call was put on hold, answered at some point, then put on hold again before the caller hung up. In those instances we'd have three records of the same callid, with 0 -> 1 -> 0 in the answered column. The best way I can think to do this is to find records with the same callid where the number of entries with 0 in the answered column is greater than those with a 1. Two questions arise from this:

Is there a better way to think about this query?
How would I go about looking for that particular scenario? I'd assume I would need to do some counting and comparisons but I haven't yet been able to land on something that works. Thanks and feel free to let me know if any of this needs clarification.

CodePudding user response：

Edit: Updated answered = 0 outside of subquery.

ROW_NUMBER() is going to be your friend on this one. You can use it determine all the scenarios where the last entry for a particular callid = 0. I'm assuming there's some timestamp for each event that occurs.

SELECT
    *
FROM (
    SELECT
        callid
        ,... --Other columns needed
        ,ROW_NUMBER() OVER (PARTITION BY callid ORDER BY <timestamp column> DESC) AS rn --Order descending so the latest entry = 1
    FROM t1
) as calls
where calls.rn = 1
and answered = 0

CodePudding user response：

Since the records always alternate 0, 1, 0, 1, 0, etc, a call whose last action was to be put on hold, ie 0, will have an odd number of rows.

Find rows having an odd count:

SELECT * from table t1
WHERE EXISTS (
    SELECT 1 from table t2
    WHERE t1.callid = t2.callid
    HAVING COUNT(*) % 2 = 1
)
ORDER BY id DESC

Or as a join, which will perform better:

SELECT t2.*
FROM (
    SELECT callid
    from table
    GROUP BY callid
    HAVING COUNT(*) % 2 = 1
) t1
JOIN table t2 ON t2.callid = t1.callid
ORDER BY id DESC