We’re rewarding the question askers & reputations are being recalculated! Read more.

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
59 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
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 ( ...
0
votes
2answers
64 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
36 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
210 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 ...
-1
votes
0answers
26 views

How to add identifier column based on date in SQL?

I have the following table (here is a sample of it): name date_time jon 10/01/2019 jon 11/01/2019 jon 12/01/2019 jon 25/01/2019 sam 03/04/2019 sam 18/04/2019 sam 19/04/2019 sam 29/04/2019 ...