Interview Handwritten SQL - Ask questions for 3 consecutive days, and finally meet!

Some time ago when I went to a company in Shanghai for an interview in autumn enrollment, I was confused. The interviewer directly asked me to write SQL on the spot. The Title I gave was also confused at that time. The Title sounded not difficult, but the interview gave you a question on the spot. It was not easy to think of in that tense atmosphere, so I filled my homework after class!

Interview Title: Write out the names of students with scores greater than or equal to 60 for three consecutive days?

Problem Analysis:

The key point of this topic is how to express it for three consecutive days. It is necessary to change your mind here. Otherwise, it is difficult to make it. I just couldn't think of it at that time. Oops! After all, I still write a little less SQL. To see the problem, three days in a row means that the dates are continuous. If you give you a table, we will find out the successive qualified students very quickly. The reason is that our brains are familiar with the table, but if you write it out with a program, you will find that your mind is suddenly stuck, so the key point is to program the thinking that we read data from the table. Give you a table, you will first locate a student in the line where he or she passed his or her first grade, and then you will find out if he or she is qualified according to the next day of the current line. If he or she is qualified, you will continue to see if he or she is qualified the next day. If he or she is qualified for three consecutive days, then we will get the results we want, well, Our thinking is like this, our programs should be like this, so to program our thinking, the key point is how SQL determines whether there is the next day, yes, you think, exists, this powerful SQL function can help us to achieve, let's start code below!

1. Building tables

create table student (
       id varchar2(20) primary key,
       name varchar2(20),
       rq date,
       score number(10)
       )

2. Insert experimental data

insert into student values('1','Rookie 1',to_date('2021-11-01','YYYY-MM-DD'),60);
insert into student values('2','Rookie 1',to_date('2021-11-02','yyyy-mm-DD'),67);
insert into student values('3','Rookie 1',to_date('2021-11-03','YYYY-MM-DD'),90);
insert into student values('13','Rookie 1',to_date('2021-11-04','YYYY-MM-DD'),90);
insert into student values('4','Rookie 2',to_date('2021-11-01','YYYY-MM-DD'),50);
insert into student values('5','Rookie 2',to_date('2021-11-02','YYYY-MM-DD'),60);
insert into student values('6','Rookie 2',to_date('2021-11-03','YYYY-MM-DD'),70);
insert into student values('7','Rookie 3',to_date('2021-11-01','YYYY-MM-DD'),50);
insert into student values('8','Rookie 3',to_date('2021-11-02','YYYY-MM-DD'),45);
insert into student values('9','Rookie 3',to_date('2021-11-03','YYYY-MM-DD'),90);
insert into student values('10','Rookie 4',to_date('2021-11-01','YYYY-MM-DD'),100);
insert into student values('11','Rookie 4',to_date('2021-11-02','YYYY-MM-DD'),100);
insert into student values('12','Rookie 4',to_date('2021-11-03','YYYY-MM-DD'),100);

3. Get the table we want to query
4.SQL implementation

select distinct name from student t1 where 
exists (select 1 from student t2 where t2.rq = t1.rq+1 and t2.name = t1.name 
and 
exists (select 1 from student t3 where t3.rq= t2.rq+1 and t3.name = t2.name)) and score >=60

Using distinct is because when SQL makes a date judgment, it will start to look for successive days from each date, because Rookie 1 is qualified for four consecutive days. If no distinct is added above, two Rookie 1 will appear. We just need to find the qualified students, so we have to repeat.

5. Execution results

It's really simple after writing, but it's not what we want. Imagine if every date is judged to be very slow, so we want to optimize, which is what we have to consider, so the focus is on!!!

Here's a particularly important and useful function in SQL, row_number() over(), window function, with this function, you can sort the data within the group. Here's an optimized override of the SQLL above us. Here's a trick for continuous processing of dates: subtract each sorting number from the date, which is exactly the same value. Just remember that.

row_number() over()

Syntax format: row_number() over(partition by grouping column order by sorting column desc)

A partition by is a grouping of fields that can be grouped like a group by, but unlike a group by followed by an order by, a partition by followed by an order by within a group can be sorted, and then row_number() returns the sort number.

For more information on the use of the OVER windowing function, refer to an article: https://blog.51cto.com/u_15057820/2650448 And then suddenly realized.

1. Grouping students by date

select s.*,row_number() over(partition by name order by rq) rn from student s where score >=60

2. Screen out students with successive qualified dates (key)

select name, count(1) as day from
(
select s.*,row_number() over(partition by name order by rq) rn from student s where score >=60
) t
group by name,rq-rn

3. Filter data for three consecutive days

select name, count(1) as day from
(
select s.*,row_number() over(partition by name order by rq) rn from student s where score >=60
) t
group by name,rq-rn  having count(1) >=3

Be accomplished! We get the results we want and know how to represent consecutive dates, so when we encounter various similar problems, such as requesting users to log on for three consecutive days, calculating transaction volume for three consecutive days, etc., we will not be confused anymore! If your friends have a more convenient and efficient way, please leave a message and learn from each other.