DA SQL 电面题

分享一个今天大厂 DA 店面 在 CODINGHIR.COM 出的SQL题：(当时没在给的时间做出，看哪位大神有最好方法）
1. Given a table "numbers" below  that has column "n" which is a sequence of integers with a step of 3, find the gap:

numbers
+--+
|n |
+--+
|3 |
|6 |
|9 |
|12|
|18|
+--+

Writre query for Expected Output:  15

SELECT a.n+3
FROM numbers a
LEFT OUTER JOIN numbers b
ON a.n = b.n-3
WHERE b.n IS NULL

一般这种题先从self join考虑，能self join解决的不用analytic function
如果有其他SQL问题可以问我，求加米~~~


补充内容 (2020-1-13 01:32):
感谢楼下提醒。这个输出会多余一行，比如原题输出是15,21, 但是应该是15. 我们加一个join和filter condition就可以了:


补充内容 (2020-1-13 01:32):
SELECT a.n+3
FROM numbers a
JOIN (SELECT MAX(n) AS max_n) FROM numbers c
LEFT OUTER JOIN number b
ON a.n=b.n-3
WHERE b.n IS NULL AND a.n<c.max_n

补充内容 (2020-1-13 01:36):
这个解只能给出每个gap的第一个gap, 如果有多个gap就不work了，比如输入是(5,14)，应该输出8,11. 但是这里只能输出8. 怎么让多gap也work，见我楼下回复。。。如果需要多gap的解，那这个电面题算难的了。

幻影奇袭 发表于 2020-1-12 13:51
SELECT a.n+3
FROM numbers a
LEFT OUTER JOIN numbers b

想问一下层主，最后b.n is NULL难道不是12 18那两行都是NULL吗？不会最后输出15和21两个数吗？谢谢已加米

smoothiethu 发表于 2020-1-12 19:24
想问一下层主，最后b.n is NULL难道不是12 18那两行都是NULL吗？不会最后输出15和21两个数吗？谢谢已加米

哎呀疏漏了，没有仔细考虑corner case。我在原回复里补充了。 我那个解，如果有2个或以上gap也不行,比如input是(5,14)，只会输出8，然而正确输出是8，11. 对多gap的情况，一个思路是构建另一个没有gap的table:
select generate_series(min(n), max(n), 3) FROM numbers
然后numbers left join这个table来找出所有gap.

另外一个思路是找出n和gap_size，比如(5, 2). 有了这个之后怎么输出答案8，11， 我也不知道。。。

with ranked AS
SELECT n, ROW_NUMBER() OVER (ORDER BY n) AS row_number
FROM numbers


with gap_size AS
select a.n, (b.n - a.n - 3)/3 AS gap_size
from ranked a
join ranked b
on a.row_number = b.row_number-1
where b.n-a.n <> 3

SELECT A.[ROW_NUMBER] * 3 AS [GAP]
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY [n]) AS [ROW_NUMBER],
[n],
LAG([n]) OVER (ORDER BY [n]) AS [PREVIOUS_VALUE]
FROM [numbers]
) AS A
WHERE A.[n] - A.[PREVIOUS_VALUE] >3;

这是我昨天写的：

With CTE As
(
Select n,
Lead(n,1,0) Over (Order by n) as NextN
From numbers
)
Select
MAX(Case When n+3 = NextN or NextN =0 then 0
else n+3 end ) As GapNumber
From CTE

大白胖胖，厉害 ：）

SELECT  n + 3
FROM    numbers as n1
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    numbers as n2
        WHERE   n2.n = n1.n + 3
        )
ORDER BY
        n
LIMIT 1

Hi rainbowzyh, good ! but what happen have more than one gap , the table data like below:
3
9
12
18
21
27

大白胖胖子, you query not right if table have more than one gap like below:
3
9
12
18
21
27

感谢分享，最近正要刷题

Shawnh19 发表于 2020-1-11 02:01
大白胖胖子, you query not right if table have more than one gap like below:
3
9

Thanks for pointing out that.