Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit90cf3be

Browse files
add 601
1 parentdea9ec4 commit90cf3be

File tree

2 files changed

+51
-0
lines changed

2 files changed

+51
-0
lines changed

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -632,6 +632,7 @@ Your ideas/fixes/algorithms are more than welcome!
632632
|608|[Tree Node](https://leetcode.com/problems/tree-node/)|[Solution](../master/database/_608.sql) | | | Medium | Union
633633
|607|[Sales Person](https://leetcode.com/problems/sales-person/)|[Solution](../master/database/_607.sql)||| Easy|
634634
|602|[Friend Requests II: Who Has the Most Friends](https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/)|[Solution](../master/database/_602.sql)||| Medium|
635+
|601|[Human Traffic of Stadium](https://leetcode.com/problems/human-traffic-of-stadium/)|[Solution](../master/database/_601.sql)||| Hard|
635636
|597|[Friend Requests I: Overall Acceptance Rate](https://leetcode.com/problems/friend-requests-i-overall-acceptance-rate/)|[Solution](../master/database/_597.sql)||| Easy|
636637
|596|[Classes More Than 5 Students](https://leetcode.com/problems/classes-more-than-5-students/)|[Solution](../master/database/_596.sql)||| Easy|
637638
|595|[Big Countries](https://leetcode.com/problems/big-countries/)|[Solution](../master/database/_595.sql)| O(n)|O(1)| Easy|

‎database/_601.sql

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
-- 601. Human Traffic of Stadium
2+
--
3+
--X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people
4+
--
5+
--Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
6+
--For example, the table stadium:
7+
--
8+
--+------+------------+-----------+
9+
--| id | date | people |
10+
--+------+------------+-----------+
11+
--| 1 | 2017-01-01 | 10 |
12+
--| 2 | 2017-01-02 | 109 |
13+
--| 3 | 2017-01-03 | 150 |
14+
--| 4 | 2017-01-04 | 99 |
15+
--| 5 | 2017-01-05 | 145 |
16+
--| 6 | 2017-01-06 | 1455 |
17+
--| 7 | 2017-01-07 | 199 |
18+
--| 8 | 2017-01-08 | 188 |
19+
--+------+------------+-----------+
20+
--
21+
--For the sample data above, the output is:
22+
--
23+
--+------+------------+-----------+
24+
--| id | date | people |
25+
--+------+------------+-----------+
26+
--| 5 | 2017-01-05 | 145 |
27+
--| 6 | 2017-01-06 | 1455 |
28+
--| 7 | 2017-01-07 | 199 |
29+
--| 8 | 2017-01-08 | 188 |
30+
--+------+------------+-----------+
31+
--
32+
--Note:
33+
--Each day only have one row record, and the dates are increasing with id increasing.
34+
35+
SELECT s1.*FROM stadiumAS s1, stadiumAS s2, stadiumas s3
36+
WHERE
37+
((s1.id+1=s2.id
38+
ANDs1.id+2=s3.id)
39+
OR
40+
(s1.id-1=s2.id
41+
ANDs1.id+1=s3.id)
42+
OR
43+
(s1.id-2=s2.id
44+
ANDs1.id-1=s3.id)
45+
)
46+
ANDs1.people>=100
47+
ANDs2.people>=100
48+
ANDs3.people>=100
49+
50+
GROUP BYs1.id

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp