The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
58 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
0answers
60 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
2answers
330 views

Find termination and rehire dates

We have a time history table named PRTH that has EmployeeID, PostDate (this is the day worked), and hours. Our ERP system does not track termination and rehire date history so I have been asked to ...
0
votes
2answers
55 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
35 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
1answer
202 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 ...
0
votes
0answers
117 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 ...