Hi,
I'm trying to understand why in certain cases, folks use calculations on Joins i.e comparing the primary key and then subtracting 1 in the join condition. As an example,
Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Write an SQL query to find all numbers that appear at least three times consecutively.
Answer:
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
Why do we use I2.Id - 1 in this case. What is the -1 part in this l1.Id = l2.Id - 1 statement doing?