Leetcode SQL

601. Human Traffic of Stadium

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:

+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+

For the sample data above, the output is:

+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+

Note:
Each day only have one row record, and the dates are increasing with id increasing.

Logic:

Here in the problem, the “people” more than 100 meant the records with people more than 100, which is the single record, not the sum of the records.\

The use the three possible combination of id’s for the ids in the outcome table

Solution:

select id, to_char(visit_date,'YYYY-MM-DD') as visit_date, people from stadium 
where people >= 100
and
(id+1 in (select id from stadium where people >= 100) and
id in (select id from stadium where people >= 100) and
id-1 in (select id from stadium where people >= 100)) or
(id+2 in (select id from stadium where people >= 100) and
id+1 in (select id from stadium where people >= 100) and
id in (select id from stadium where people >= 100)) or
(id in (select id from stadium where people >= 100) and
id-1 in (select id from stadium where people >= 100) and
id-2 in (select id from stadium where people >= 100))
order by id, visit_date;

Link

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Compiling C Files Using the GCC

Where in a Python List? Let’s Find Out This Item [Simple Tutorial] -

Lessons learned from Load Testing

Craft(ing) Success with OrthoFi

My CKAD preparations

This week in #Scala (Dec 27, 2021)

From ISO to Application VM — Using Packer, Ansible and Jenkins to automate VM delivery

My personal journey to code…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jen-Li Chen

Jen-Li Chen

In love with telling stories with data

More from Medium

LeetCode SQL|178. Rank Scores

Prepare your SQL interview in less than 10 minutes — Part 1

937. Reorder Data in Log Files

Data Structure — Linked List