[SOLVED] Select with gap in dates

Issue

It is possible to make a selection with a pre-defined interval of days between records, ex:

+------+----------------+--------+
| user | date           |  Data  |
+----- +----------------+--------+
|    1 | 2022-01-01     |   1    |
|    1 | 2022-01-02     |   5    |
|    1 | 2022-01-03     |   2    |
|    1 | 2022-01-06     |   3    |
|    1 | 2022-01-07     |   7    |
|    1 | 2022-01-08     |   1    |
|    1 | 2022-01-09     |   2    |
+------+----------------+--------+

GIVE ME ALL RECORDS WITH 2 DAYS INTERVAL FOR EACH DATE

(There may be gaps in the records at the bank as happened between 2022-01-03 and 2022-01-06)

Result:

+------+----------------+--------+
| user | date           |  Data  |
+----- +----------------+--------+
|    1 | 2022-01-01     |   1    |
|    1 | 2022-01-06     |   3    |
|    1 | 2022-01-09     |   2    |
+------+----------------+--------+

But this value of 2 days can be changed to 7 days, 30 etc.

Currently I’m doing this manually by code, I get the start date, add + x days, do the select, add + x days, do the other select until the interval ends, but this is extremely expensive and the process takes minutes.

Is there any way to do this in a single query?

Solution

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY `date`) rn
    FROM tablename
)
SELECT * 
FROM cte
WHERE rn MOD 3 = 1

Answered By – Akina

Answer Checked By – Jay B. (BugsFixing Admin)