SQL Challenges-1: Consecutive Numbers

SQL Challenges-1: Exercise-4 with Solution

From the following table, write a SQL query to find the marks, which appear at least thrice one after another without interruption. Return the number.

Input:

Table: logs

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE IF NOT EXISTS logs (student_id int, marks int);
TRUNCATE TABLE logs;
INSERT INTO logs (student_id, marks) VALUES ('101', '83');
INSERT INTO logs (student_id, marks) VALUES ('102', '79');
INSERT INTO logs (student_id, marks) VALUES ('103', '83');
INSERT INTO logs (student_id, marks) VALUES ('104', '83');
INSERT INTO logs (student_id, marks) VALUES ('105', '83');
INSERT INTO logs (student_id, marks) VALUES ('106', '79');
INSERT INTO logs (student_id, marks) VALUES ('107', '79');
INSERT INTO logs (student_id, marks) VALUES ('108', '83');
select * from logs;
SELECT DISTINCT L1.marks AS  ConsecutiveNums
FROM (logs L1 JOIN logs L2 ON L1.marks = L2.marks AND L1.student_id = L2.student_id-1)
JOIN logs L3 ON L1.marks = L3.marks AND L2.student_id = L3.student_id-1;

Sample Output:

ConsecutiveNums|
---------------|
             83|

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Solution-1:

SELECT DISTINCT L1.marks AS  ConsecutiveNums
FROM (logs L1 JOIN logs L2 ON L1.marks = L2.marks AND L1.student_id = L2.student_id-1)
JOIN logs L3 ON L1.marks = L3.marks AND L2.student_id = L3.student_id-1;

Solution-2:

SELECT DISTINCT L1.marks AS ConsecutiveMarks
FROM logs AS L1, logs AS L2, logs AS L3
WHERE L1.student_id = L2.student_id +1 
AND L1.student_id = L3.student_id +2 
AND L1.marks = L2.marks AND L1.marks = L3.marks;

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: N^th Highest Sale amount.
Next: Duplicate Emails.