This MySQL function, `GetLongestConsecutiveSequenceLength`, calculates the length of the longest sequence of consecutive integers present in a 'numbers' table. It iterates through the numbers, sorted in ascending order,...

The `GetLongestConsecutiveSequenceLength` function aims to find the maximum length of a contiguous sequence of integers within the `numbers` table. It initializes `max_length` and `current_length` to 0. A cursor `cur` is declared to iterate through the `numbers` table, ordered by the `number` column. This ordering is critical for identifying consecutive sequences. Before starting the loop, it checks if the table is empty; if so, it returns 0 immediately. Inside the loop, for each `current_number` fetched, it compares it with the `previous_number`. If it's the first number or if `current_number` is exactly one greater than `previous_number`, `current_length` is incremented, extending the current sequence. Otherwise, the sequence is broken, and `current_length` is reset to 1 (for the current number starting a new potential sequence). After updating `current_length`, it's compared with `max_length`, and `max_length` is updated if `current_length` is larger. Finally, `previous_number` is updated to `current_number` for the next iteration. The time complexity is O(N log N) due to the sorting step required by the cursor, where N is the number of rows in the `numbers` table. The space complexity is O(1) if we consider the cursor's internal state as constant, or O(N) if the sorted data needs to be materialized. The correctness relies on the sorted iteration and the state variables (`current_length`, `max_length`) accurately tracking the sequence progression.

FUNCTION GetLongestConsecutiveSequenceLength(): max_length = 0 current_length = 0 previous_number = NULL current_number = NULL done = FALSE IF numbers table is empty THEN RETURN 0 END IF DECLARE cursor FOR SELECT number...

This MySQL function, `GetNthHighestSalary`, efficiently retrieves the Nth highest distinct salary from an 'employees' table. It utilizes the `DENSE_RANK()` window function to assign a rank to each unique salary in descen...

The function `GetNthHighestSalary` takes an integer `N` as input and returns the Nth highest salary as a decimal. First, it validates that `N` is a positive integer; if not, it raises a custom SQL error. The core logic involves a subquery that selects salaries from the `employees` table and assigns a rank using `DENSE_RANK() OVER (ORDER BY salary DESC)`. `DENSE_RANK()` is crucial here because it assigns consecutive ranks to salaries, even if there are ties (e.g., two employees with the same highest salary both get rank 1, and the next distinct salary gets rank 2). The outer query then selects the `salary` where `salary_rank` equals `N`. `LIMIT 1` is used to ensure only one row is returned, although `DENSE_RANK` should inherently provide unique ranks for distinct salaries. If no salary matches the `N`th rank (meaning `N` is larger than the number of distinct salaries), `nth_salary` will be NULL, and the function returns NULL. The time complexity is dominated by the window function, which is typically O(M log M) or O(M) depending on the database implementation and indexing, where M is the number of employees. The space complexity is O(M) in the worst case for storing intermediate results for ranking.

FUNCTION GetNthHighestSalary(N): IF N is not positive THEN RAISE ERROR 'N must be positive' END IF SELECT salary INTO nth_salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank FROM employee...

This scenario involves using a recursive CTE in MySQL to calculate the total quantity of each component required for a top-level product in a Bill of Materials (BOM). BOMs represent the hierarchical structure of products...

The recursive CTE `BomExpansion` starts with the direct components of a specified `parent_part` (the anchor member). The recursive member then joins the `bom` table with the CTE itself, expanding sub-assemblies. Crucially, `be.quantity_per_parent * b.quantity_per_parent` multiplies the quantities at each level to correctly calculate the total quantity of a component needed for the original top-level product. The `level` column tracks the depth of the recursion. The final `SELECT` statement aggregates the `quantity_per_parent` for each `child_part` across all levels of the expansion. A `WHERE be.level < 10` clause acts as a safety break to prevent infinite recursion in case of cyclic dependencies in the BOM data. Time complexity can be significant, potentially O(N*D) where N is the number of BOM entries and D is the maximum depth of the hierarchy, as each entry might be processed multiple times. Space complexity is O(N*D) for storing intermediate results.

1. Define a recursive CTE named `BomExpansion`. 2. Anchor Member: Select direct components of the target `parent_part`, assign `level` 1. 3. Recursive Member: Join `bom` table with `BomExpansion` on `b.parent_part = be.c...

This scenario focuses on identifying duplicate records within a MySQL table using standard SQL aggregation techniques. Specifically, it targets duplicates based on a composite key (email and phone number). This is a comm...

The query first filters out rows where `email` or `phone_number` is NULL, as `GROUP BY` treats NULLs as distinct values by default, and we're looking for exact matches. It then groups the remaining rows by the combination of `email` and `phone_number`. The `COUNT(*)` aggregate function counts the number of rows in each group. The `HAVING COUNT(*) > 1` clause filters these groups, returning only those combinations that appear more than once, indicating duplicates. The time complexity is typically O(N log N) due to the sorting involved in grouping, or O(N) if an appropriate index exists. Space complexity is O(K) where K is the number of distinct `(email, phone_number)` combinations.

1. Select `email`, `phone_number`, and the count of occurrences. 2. From the `customers` table. 3. Filter out rows where `email` or `phone_number` is NULL. 4. Group the results by `email` and `phone_number`. 5. Use `HAVI...

This scenario demonstrates how to use a recursive Common Table Expression (CTE) in MySQL to query hierarchical data, such as an organizational chart. Recursive CTEs are essential for tasks like finding all subordinates o...

A recursive CTE consists of an anchor member (the base case) and a recursive member (the step that calls itself). The anchor member `SELECT`s the starting rows (e.g., top-level employees). The recursive member `SELECT`s rows that are related to the results of the previous iteration, joining the `employees` table with the CTE itself (`EmployeeHierarchy`). The `UNION ALL` combines the results. The recursion stops when the recursive member returns no rows. A `level` column is added to track the depth. The `WHERE eh.level < 10` clause acts as a safety termination condition to prevent infinite loops in case of data cycles, limiting the recursion depth to 10. Time complexity can vary greatly depending on the depth and breadth of the hierarchy, potentially O(N) where N is the number of nodes visited. Space complexity is also O(N) for storing the intermediate results of the CTE.

1. Define a recursive CTE named `EmployeeHierarchy`. 2. Anchor Member: Select employees with `manager_id` IS NULL, assign `level` 0. 3. Recursive Member: Join `employees` table with `EmployeeHierarchy` on `e.manager_id =...

This scenario focuses on implementing full-text search in MySQL. It involves creating a table with a full-text index on relevant columns and then writing a function to query this index. This is crucial for applications r...

The `FULLTEXT` index in MySQL allows for efficient searching of text data. The `MATCH()` and `AGAINST()` functions are used to query this index. `MATCH()` specifies the columns to search, and `AGAINST()` provides the search term. `IN NATURAL LANGUAGE MODE` interprets the search string as a natural phrase. The function returns a table of matching products. The time complexity for indexing is typically proportional to the size of the data, while searching is much faster, often sub-linear, depending on the index structure. Space complexity is for storing the index, which can be significant for large text fields.

1. Define a table `products` with columns for ID, name, and description. 2. Add a `FULLTEXT` index on `product_name` and `description` columns. 3. Create a stored function `SearchProducts` that accepts a `keyword`. 4. In...

This scenario focuses on calculating a rolling average for time-series data in MySQL. It involves using the `AVG()` window function with a specific frame clause to compute the average sales over a preceding 7-day period....

The query calculates the 7-day rolling average using the `AVG()` window function. `ORDER BY sale_date` ensures that the average is computed chronologically. The `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` clause defines the window frame: it includes the current row and the six rows immediately before it in the sorted sequence. This creates a sliding window of 7 days. For the first six days, the average is calculated using fewer than seven data points because there are not enough preceding rows. The time complexity is typically O(N log N) due to the sorting required for `ORDER BY` within the window function, or O(N) if the data is already sorted by `sale_date`. Space complexity is O(N) for intermediate results.

1. Select the `sale_date` and `total_sales`. 2. Calculate the average of `total_sales` using a window function. 3. Order the window by `sale_date`. 4. Define the window frame to include the current row and the 6 precedin...

This scenario focuses on optimizing large tables in MySQL using partitioning, specifically range partitioning based on a timestamp. By dividing a large `event_logs` table into smaller, manageable partitions based on time...

Range partitioning divides rows into partitions based on a continuous range of values in a partitioning key. Here, `event_timestamp` is used, and `UNIX_TIMESTAMP()` converts it to an integer for partitioning. The `CREATE TABLE` statement defines partitions for each month of 2023. The `SELECT` query filters events within a specific date range. MySQL's query optimizer can perform 'partition pruning,' identifying which partitions are relevant to the query based on the `WHERE` clause and the partitioning scheme. Only the relevant partitions are scanned, drastically reducing I/O and improving performance. The time complexity for queries that can utilize partition pruning is significantly reduced, potentially becoming O(M) where M is the number of rows in the relevant partitions, instead of O(N) for the entire table. Space complexity is distributed across partitions.

1. Define a table `event_logs` with columns including `event_id`, `event_timestamp`, `event_type`, `event_data`. 2. Partition the table by `RANGE` on `UNIX_TIMESTAMP(event_timestamp)`. 3. Define partitions for specific d...