Consecutive Numbers
Amazon
Problem Statement
Amazon log systems record numerical events in sequence. An anomaly is defined as any number that appears at least three times consecutively in the log.
Write an SQL query to find all numbers that appear at least three times consecutively.
Rules:
- A number is consecutive if it appears in rows with consecutive id values.
- Return the result table with the column ConsecutiveNums.
- Use DISTINCT to avoid duplicate results for numbers that appear in a sequence longer than three.
- Results must be sorted by ConsecutiveNums in ascending order.
Tests your understanding of
Basic SQL, Window Functions, Self Join, LEAD and LAG
Input Tables
logs
| id(INTEGER) | num(VARCHAR) |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
Expected Output
| ConsecutiveNums(VARCHAR) |
|---|
| 1 |
| 2 |
Tags
MediumBasic SQLWindow FunctionsSelf JoinLEADLAG
15-20 min
45%