180. Consecutive Numbers – Leetcode Solutions

Example 1:

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

import pandas as pd

def consecutive_numbers(logs: pd.DataFrame) -> pd.DataFrame:
    # Sort the DataFrame by 'id' column in ascending order
    logs = logs.sort_values(by='id', ascending=True)
    
    # Initialize an empty list to store numbers that appear consecutively
    consecutive_numbers_list = []
    
    # Iterate through the list of numbers, checking for consecutive occurrences
    for i in range(len(logs) - 2):
        # Check if the current number is the same as the next two numbers
        # and if their ids are consecutive
        if (logs['num'].iloc[i] == logs['num'].iloc[i+1] == logs['num'].iloc[i+2] and
            logs['id'].iloc[i+1] == logs['id'].iloc[i] + 1 and
            logs['id'].iloc[i+2] == logs['id'].iloc[i] + 2):
            # If conditions are met, add the number to the list of consecutive numbers
            consecutive_numbers_list.append(logs['num'].iloc[i])
    
    # Remove duplicate entries by converting the list to a set, then back to a list
    consecutive_numbers_list = list(set(consecutive_numbers_list))
    
    # Convert the list of consecutive numbers to a DataFrame
    result_df = pd.DataFrame(consecutive_numbers_list, columns=['ConsecutiveNums'])
    
    # Return the final result DataFrame
    return result_df

SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT num,
           LEAD(num, 1) OVER (ORDER BY id) AS next1,
           LEAD(num, 2) OVER (ORDER BY id) AS next2,
           LEAD(id, 1) OVER (ORDER BY id) AS next_id1,
           LEAD(id, 2) OVER (ORDER BY id) AS next_id2,
           id
    FROM Logs
) AS subquery
WHERE num = next1 AND num = next2 
AND next_id1 = id + 1 AND next_id2 = id + 2;

-- The outer query selects distinct numbers that appear consecutively at least three times
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    -- The subquery generates the current number, the next two numbers, and their corresponding IDs
    SELECT 
        num,
        LEAD(num, 1) OVER (ORDER BY id) AS next1,           -- Number in the next row
        LEAD(num, 2) OVER (ORDER BY id) AS next2,           -- Number two rows ahead
        LEAD(id, 1) OVER (ORDER BY id) AS next_id1,         -- ID of the next row
        LEAD(id, 2) OVER (ORDER BY id) AS next_id2,         -- ID of the row two steps ahead
        id                                               -- Current ID for reference
    FROM Logs
) AS subquery
-- Filter the results where the current number matches the next two numbers
-- and the IDs are consecutive
WHERE num = next1 
  AND num = next2 
  AND next_id1 = id + 1 
  AND next_id2 = id + 2;