Skip to main content

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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
68 views

Total days on consecutive date records up to gap

I have a classic Gaps and Islands issue whereby I need to total the time across the most recent records as far back as the first gap in dates Looking at Find Consecutive Date in SQL using 2 date ...
Judy D's user avatar
  • 3
0 votes
2 answers
359 views

select row number increment on value change compared to previous return result

I'm experimenting with Postgresql queries a bit and am trying to figure out if it's possible to write a query that would increment a row number counter when returned value changes. Example SOURCE (...
Domen Vrankar's user avatar
1 vote
1 answer
46 views

Identify if the sequence of visits to an area id was interrupted at any point

I have a PostgreSQL table df with the following information: area_id trip_nr timestamp a 3 29/06/2022 17:18:03 a 2 29/06/2022 17:18:02 a 1 29/06/2022 17:18:01 b 1 28/06/2022 19:21:03 c 3 28/06/...
Ruan's user avatar
  • 35
0 votes
1 answer
97 views

Update row data with dynamic lag

I have a table of items and restaurants. We assume that if the restaurant entry is missing, it has to be completed with the last non-null restaurant value in the previous rows (as defined by ascending ...
eddie's user avatar
  • 101
1 vote
1 answer
180 views

SELECT / DELETE rows where consecutive null value count is greater than N

I have a table of the following structure, where all columns except the last are non-null: ID receiver send_time recv_time 1 A 00:00:00 00:00:01 2 A 00:00:01 NULL 3 A 00:00:02 NULL 4 A 00:00:03 ...
Edgxxar's user avatar
  • 13
1 vote
1 answer
30 views

Merging wifi session data if time between them is less than 15 minutes

I am trying to process network logs and join sessions together if the time between them is less than 15 minutes. The relevant fields are start time, end time, mac address, and wifi access point. I am ...
lpscott's user avatar
  • 13
0 votes
1 answer
2k views

SQL (Postgres) Filling up missing rows with values from previous row and calculating on result set

I have got this dataset: id | block_number | value | name ----+--------------+-------+------ 2 | 47 | 100 | one 3 | 52 | 200 | one 4 | 58 | 120 | one 5 | ...
Westcoaster's user avatar
0 votes
1 answer
257 views

Count the number of days an order has been at a specific status

I can't figure out how to write a query that returns the results I need. Below is a table that list in chronological order the status of an order on specific days and time. An order can change from ...
Kram_Koorbse's user avatar
-3 votes
2 answers
81 views

Rank over discontinuous blocks of rows [closed]

I have the following table Logs: Id Char 1 a 2 a 3 a 4 b 5 b 6 a 7 a 8 c I want to get something like: Id Char Rank 1 a 1 2 a 2 3 a 3 4 b 1 5 b 2 6 a 1 7 a 2 8 c 1 A MS-SQL-Server ...
Wasim Ali's user avatar
2 votes
3 answers
399 views

Find out students max no of times not enrolled consecutively in a subject

This is my sample data set. Looking for code in MySQL Desired output Student Times abc 3 (Student abc didn't enroll for any subject consecutively for T3,T4 in 2021 and then for T1 in 2022) xyz 4 (...
PS_22's user avatar
  • 23
2 votes
1 answer
58 views

Identify unique trips for each user where walking is followed by trip start

I have a table trips with the following information Row User Timestamp Event 1 1 29/06/2022 17:18:03 Walking 2 1 29/06/2022 17:18:02 Walking 3 1 29/06/2022 17:18:01 Start 4 1 28/06/2022 19:21:03 ...
Ruan's user avatar
  • 35
1 vote
1 answer
33 views

Identifying/Counting Unique Claims

I need your help to get a count of unique claims from a table where claim_id and claim_id2 can occasionally crisscross So for example, given a table with the following rows: Table scripts: create ...
Gar's user avatar
  • 11
0 votes
0 answers
218 views

Calculate the sum of minutes between statuses Clickhouse

There is a table in ClickHouse that is constantly updated, format: date_time | shop_id | item_id | status | balance --------------------------------------------------------------- 2022-09-09 ...
Kirill_K's user avatar
2 votes
2 answers
547 views

How to remove only adjacent duplicates in an array?

While aggregating an array, I need to remove empty strings and then combine all adjacent identical values. E.g.: ["","product","product","","product",&...
AJ AJ's user avatar
  • 125
2 votes
1 answer
981 views

Cumulative running SUM for date ranges with gaps

I am looking for assistance to get a running sum of the difference between two dates that are on separate rows. Table looks like: client_id name autopay_status contract_id start_date end_date 1 ...
michaelosmith's user avatar
2 votes
1 answer
211 views

Aggregate query for pairs of arrays of events

db<>fiddle for all of the data and queries below I have a table events with the following structure: create table events ( correlation_id char(26) not null, user_id bigint, ...
Moshe Katz's user avatar
0 votes
1 answer
1k views

Find Consecutive Date in SQL using 2 date columns

I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per ...
Jane 's user avatar
0 votes
1 answer
3k views

Query to find the count of active days (days with status = ON) between specified dates

I have a table as you can see below : create table z_test_duration ( Days date, Status char(8) ); Sample data is as below : Days Status 1/1/2022 on 1/2/2022 on 1/3/2022 on 1/4/2022 off ...
Pantea's user avatar
  • 1,432
2 votes
2 answers
587 views

Finding gaps between the values of a column in MySQL

At a table of movie actors, CREATE TABLE ArtistMap ( ArtistID int(11) unsigned NOT NULL, MovieID int(11) unsigned NOT NULL, Year year, INDEX(MovieID), INDEX(Year), PRIMARY KEY(ArtistID,MovieID) ) ...
Googlebot's user avatar
  • 4,531
2 votes
1 answer
924 views

Get rows grouped by a foreign key with count of consecutive values

I have a SQL Server database with a transactions table with client_id, date, and is_cancelled. I'm trying to get the client_ids that have 3 or more transactions in a row marked as is_cancelled, along ...
Healyhatman's user avatar
0 votes
1 answer
985 views

Retrieve gaps in dates in SQL Server

I'm trying to figure out how to retrieve gaps in time-series data. Below is my example. CREATE TABLE MYTABLE ([ID] int, [DATE] DATE) ; INSERT INTO MYTABLE ([ID], [DATE]) VALUES (1, '2022-...
Mr. Geologist's user avatar
0 votes
1 answer
362 views

Possible to GROUP BY across different columns in mariadb/mysql

I have a table full of events similar to this: `date` | start_time | end_time | length -------------------------------------------- 2022-01-10 | 07:00:00 | 16:00:00 | 9 2022-01-11 | 07:00:...
MrWainscotting's user avatar
1 vote
1 answer
1k views

Postgres find gaps between one date range and others

My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one. In my case is also guaranteed that &...
Dmitry K.'s user avatar
  • 143
2 votes
1 answer
565 views

T-SQL : Slicing time periods by related time periods

Consider the following table that gives the validity dates for which an Item is associated to something called a Demand_Unit, and a certain Market : Item_ID Start_Date End_Date Demand_Unit Market X ...
Yass T's user avatar
  • 23
0 votes
1 answer
192 views

T-SQL : Grouping based on chronological switch periods

I have the below table extracted from a Type 2 Slowly Changing dimension. I want to group rows to only have distinct classifications and their respective 'switch' time periods in a chronological order....
Yass T's user avatar
  • 23
1 vote
2 answers
929 views

How to select the latest continuous set of rows of a certain type?

(Using PostgreSQL) I'm looking to select the latest set of continuous (by date) rows that have a code 'A'. The latest row is always missing the "To Date" as it is assumed to be current. And ...
ahbarnum's user avatar
3 votes
1 answer
157 views

How do I aggregate related rows when there's no group key?

I'm using MariaDB 10.6 and have a table: CREATE TABLE velocities ( `state` int(8) NOT NULL, `timestamp` datetime NOT NULL, `velocity` decimal(5,2) NOT NULL, `name` varchar(15) NOT NULL ) ...
samuel02's user avatar
  • 133
0 votes
1 answer
177 views

Determine Available Blocks of Contiguous Ids

We have a SQL Server table that has an int autoincrement primary key column on it. The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
Manny Siddiqui's user avatar
0 votes
1 answer
2k views

Count of null rows preceding each non-null value

I need to answer the following question: What is the average number of email sends it take before a customer opens an email? I know I need to count the number of rows between each Open_Date in order ...
r2k2's user avatar
  • 3
1 vote
1 answer
1k views

Pick the first timestamp before a gap, but the last one of the day if there is no suitable gap

I have a TIMESTAMP column: dates 2021-06-24 05:47:05 2021-06-24 09:47:05 2021-06-24 13:47:05 2021-06-24 17:47:05 I want to pick the first timestamp of a given day that is 3 hours or more before the ...
a_dog_with_no_master's user avatar
0 votes
1 answer
73 views

how to generate a sequence of values with differents formats in PostgreSQL

I want to find rows missing from a sequence of values in my table with differents formats. For an example, look at this. I want to find the missing lines via c_order_id and documentno. the one with '...
Franck White's user avatar
0 votes
2 answers
1k views

How to find values missing from a sequence?

I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing. I want to find the missing lines via c_order_id and documentno. I ...
Franck White's user avatar
0 votes
2 answers
53 views

Detect multiple valid date periods for same id, interleaved with non-valid periods

My table structure and some sample data (test table), as well as my desired output (results) are available here. When I do SELECT distinct colony, min(date_check), max(date_check) from test where ...
FranckT's user avatar
  • 17
3 votes
1 answer
1k views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
sealabr's user avatar
  • 249
3 votes
1 answer
1k views

How to collapse rows with individual, contiguous dates into single rows with date range start and end

Given a data set like this: Id Date Value 1 01/01/2021 100 1 01/02/2021 100 1 01/03/2021 100 ... ... (rows omitted for brevity) ... 1 12/29/2021 100 1 12/30/2021 100 1 12/31/2021 100 2 01/01/...
Brian M.'s user avatar
1 vote
1 answer
209 views

Query to find if there are more than X occurences within any period of given length minutes

I have spent many hours thinking about a solution for my problem but I give up. Let's imagine a table user_id | occurred_at -- OK match example 1 | 2020-01-01 08:00:00 <- First match of ...
micper's user avatar
  • 13
2 votes
2 answers
175 views

Gaps and Islands

I have a table with StartDate and EndDate. I wanted to get the missing Date and The script I have does it. But if given this data: LocationID StartDate EndDate ---------- ---------- ---------- ...
Jeni's user avatar
  • 23
4 votes
2 answers
3k views

Postgres lag() function has huge impact on query

As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then ...
VirtApp's user avatar
  • 181
-1 votes
1 answer
1k views

Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id

I have a table like this one, that shows payments from each client, for different dates: | stream_datetime | customer_id | order_status | rn | |:----------------:|:-----------:|:------------:|:--:| | ...
igorkf's user avatar
  • 101
2 votes
2 answers
298 views

Group by time interval and output the source and destination station_id and count

I am stuck with a query: CREATE TABLE public.bulk_sample ( serial_number character varying(255), validation_date timestamp, -- timestamp of entry and exit station_id integer, ...
Deepan Kaviarasu's user avatar
1 vote
1 answer
796 views

SQL Server - Get the Date Ranges

I have a table with start date and end date. For example, PlaceID | StartDate | EndDate 1 | 2020-01-01 | 2020-01-04 1 | 2020-01-10 | 2020-01-15 2 | 2020-01-03 | 2020-01-06 2 | ...
user avatar
1 vote
1 answer
1k views

How to collapse contiguous single day start/end date ranges in MySQL

(edited - I oversimplified the original problem) I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data: INSERT INTO ...
lfjeff's user avatar
  • 101
0 votes
1 answer
81 views

How do I find the first row of the longest occurrences of repeating numbers?

I have a table with the following structure: id,center,shelf_id,occupied,remaining 1,East,1_1_1,1,0 2,East,1_1_2,1,0 3,East,1_1_3,1,4 4,East,1_1_4,1,0 5,East,1_1_5,1,0 6,East,1_2_1,1,3 7,East,1_2_2,1,...
andyy15's user avatar
0 votes
1 answer
182 views

Gaps and Islands Across Fields With Start and End Timestamps

I am working with GPS data which I want to group by locations (to six decimal places of the coordinate value) and generate from and until times for. For simplicity in this example I'm making the ...
sgaw's user avatar
  • 101
0 votes
1 answer
38 views

Need to identify non contigious rows

My table structure is as follows: create table daily_summary ( id uuid default uuid_generate_v4() not null primary key, machine bigint ...
Pavanraotk's user avatar
0 votes
1 answer
201 views

MSSQL - convert list of datetimes in a date range

I have one system which stores data like: CREATE TABLE Reservations ([UserId] int, [RoomId] int, [TypeId] int, [Date] datetime) ; Every row in the table means that Room is reserved for User of ...
Tom C.'s user avatar
  • 33
0 votes
1 answer
261 views

Gap Finding - Dates

Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps. Data is timestamp without timezone formatted and an ...
Alex Vermeulen's user avatar
1 vote
1 answer
219 views

Merging Continous Date Ranges with upto 63 days in between

I am trying to write a query that will take multiple date ranges and combine them into a contiguous date range. The catch is that I have to allow for up to 63 days in between the last termination date ...
JOSH W's user avatar
  • 27
1 vote
1 answer
123 views

Determine Order Streak

For a particular customer, I have data returned by a query that looks like this: OrderDate OrderCount 2020-01-12 0 2020-01-13 17 2020-01-14 22 2020-01-15 21 2020-01-16 30 2020-01-17 12 2020-...
Dicer's user avatar
  • 113
1 vote
2 answers
122 views

Query for condition over time

Say we have a table setup like this: +---------+--------------+---------------+ | id text | time integer | value decimal | +---------+--------------+---------------+ | 1 | 1 | ...
dotconnor's user avatar
  • 135