Welcome to Subscribe On Youtube
Question
Formatted question description: https://leetcode.ca/all/1613.html
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.
Example 1:
Input: Customers table: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Alice | | 4 | Bob | | 5 | Charlie | +-------------+---------------+ Output: +-----+ | ids | +-----+ | 2 | | 3 | +-----+ Explanation: 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
- CTE stands for common table expression
- example: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15#examples
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);