Welcome to Subscribe On Youtube

Question

Formatted question description: https://leetcode.ca/all/1613.html

 1613. Find the Missing IDs

 SQL Schema

 Table: Customers

 +---------------+---------+
 | Column Name   | Type    |
 +---------------+---------+
 | customer_id   | int     |
 | customer_name | varchar |
 +---------------+---------+
 customer_id is the primary key for this table.
 Each row of this table contains the name and the id customer.


 Write an SQL query to find the missing customer IDs.
 The missing IDs are ones that are not in the Customers table but are in the range between 1 and the maximum customer_id present in the table.

 Notice that the maximum customer_id will not exceed 100.

 Return the result table ordered by ids in ascending order.

 The query result format is in the following example.



 Customer table:
 +-------------+---------------+
 | customer_id | customer_name |
 +-------------+---------------+
 | 1           | Alice         |
 | 4           | Bob           |
 | 5           | Charlie       |
 +-------------+---------------+

 Result table:
 +-----+
 | ids |
 +-----+
 | 2   |
 | 3   |
 +-----+
 The maximum customer_id present in the table is 5, so in the range [1,5], IDs 2 and 3 are missing from the table.

Algorithm

example sql:

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear;

Code

SQL

WITH RECURSIVE CTE AS(
    SELECT 1 AS 'id', MAX(c.customer_id) AS 'Max_Id'
    FROM Customers c
    UNION ALL
    SELECT id+1, Max_Id
    FROM CTE
    WHERE id < Max_id
)

SELECT id AS 'ids'
FROM CTE c
WHERE c.id NOT IN (SELECT customer_id FROM Customers)
ORDER BY 1 ASC

Option-2. Generate all numbers from 1 to 100, and select the numbers in the range from 1 to 100 and not in customer_id.

# Write your MySQL query statement below
select ids 
from 
(
    select x * 10 + y + 1 as ids 
    from 
    (
        select 0 as x union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
    ) as tens, 
    (
        select 0 as y union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
    ) as ones
    order by ids
) as num
where ids < (select max(customer_id) from Customers)
and ids not in (select distinct customer_id from Customers);

All Problems

All Solutions