Questions tagged [gaps-and-islands]

Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

7 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
67 views

How to perform a running count on null values that resets on non-null values

I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function ...
1
vote
1answer
258 views

How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows: 42, 1 ...
1
vote
0answers
61 views

Finding the largest island size (redshift)

Current sql code will sum up all the max island sizes into one. I'm trying to only find what the max island size is. select deviceid, grp -- min(readdate) as mn, max(readdate) as mx from ( ...
1
vote
1answer
230 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
0
votes
2answers
91 views

Mysql consecutive days user wise

Table 'present' +--------+------------+ | emp_id | pre_date | +-----------+---------+ | 002 | 2019-04-01 | | 001 | 2019-04-21 | | 001 | 2019-04-15 | | 001 | 2019-04-19 | | 002 | 2019-...
0
votes
0answers
41 views

how to counting time in partition (MYSQL)

I looking for how to counting time by partition in condition if value=1 occurs consecutively 3 or more times count 1 activity. Showing result on this picture
0
votes
0answers
119 views

Find consecutive entries in a table and group into another table

So ideally I'd like a table output such as: consecutive_days | max_date for a given user where consecutive days is the number of days a person logged something and max_date was the last date in ...