Why GROUP BY num HAVING "count == distance + 1" AND "count >=3 " solution doesn't work?


  • 0
    Y

    The idea is that consecutive ids are arithmetic progression of 1, so the distance between Min and Max of id should be exactly count -1 (since ids are integer, their can't be more or less integer within the range to make up the exact count).

    SELECT Num AS ConsecutiveNumbers
    FROM Logs
    GROUP BY Num
    HAVING COUNT(*) >= 3 AND MAX(Id) - MIN(Id) + 1 = COUNT(*) ;
    

    16/21 passed.
    This is one of the tests that fail:

    Input:

    {"headers": {"Logs": ["Id"
    "Num"]}
    "rows": {"Logs": [[1
    1]
    [2
    1]
    [3
    1]
    [4
    2]
    [5
    1]
    [6
    2]
    [7
    2]]}}

    Output:

    {"headers": ["ConsecutiveNumbers"], "values": []}

    Expected:

    {"headers": ["ConsecutiveNums"], "values": [[1]]}


  • 4

    In this particular case it doesn't work because you should have written COUNT(*) - 1 instead of COUNT(*) + 1. In your example Max(Id) - Min(Id) = 3-1 = 2 and COUNT(*) + 1 = 3 + 1 = 4.

    However, it won't work in other tests. Consider something like

    1 1
    2 1
    3 1
    4 4
    5 1
    

    You now have Max(Id) = 5 (and it could be anything), Min(Id) = 1.


  • 0
    Y

    Thanks a lot for pointing that out. I've changed the question according to your comment. The question remains because in the example you provide, GROUP BY will get two sets:
    {(1, 1)(2, 1)(3, 1)(5, 1)} and {(4, 4)}
    The first predicate of HAVING "count(*) >= 3" will filter out the second set.
    For the first set, max - min + 1 = 5 - 1 + 1 = 5 != count = 4, so it will also be filtered out.
    I think there's other reason.


  • 0

    Exactly. The first set will also be filtered out. And it should not be filtered out because it has a subset with three consecutive 1s, which should be returned as the answer, but it won't be.


  • 0
    Y

    That make sense. Thanks.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.