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.
156
questions
2
votes
1
answer
46
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
...
- 23
1
vote
1
answer
25
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 ...
- 11
0
votes
0
answers
121
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 ...
- 1
2
votes
2
answers
218
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",&...
- 25
2
votes
1
answer
305
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
...
2
votes
1
answer
83
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,
...
- 175
0
votes
1
answer
349
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 ...
- 1
0
votes
1
answer
1k
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
...
- 1,390
0
votes
0
answers
32
views
Group datetimes by "session" logic (gap between events) [duplicate]
There are a couple similar questions out there -- this is NOT about grouping things by 30 minute intervals or similar.
This question is similar to Google Analytics logic. A "session" is ...
- 1,583
2
votes
2
answers
314
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)
) ...
- 4,389
2
votes
1
answer
370
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 ...
- 123
0
votes
1
answer
165
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-...
0
votes
1
answer
233
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:...
1
vote
1
answer
583
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 &...
- 133
2
votes
1
answer
255
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
...
- 23
0
votes
1
answer
108
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....
- 23
1
vote
2
answers
302
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 ...
- 13
3
votes
1
answer
110
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
) ...
- 133
0
votes
1
answer
125
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 ...
0
votes
1
answer
774
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 ...
- 3
1
vote
1
answer
526
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 ...
0
votes
1
answer
63
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 '...
0
votes
2
answers
637
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 ...
0
votes
2
answers
45
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 ...
- 17
3
votes
1
answer
744
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 ...
- 239
3
votes
1
answer
918
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/...
- 31
1
vote
1
answer
165
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 ...
- 13
2
votes
2
answers
157
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
---------- ---------- ----------
...
- 23
4
votes
2
answers
2k
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 ...
- 181
-1
votes
1
answer
481
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 |
|:----------------:|:-----------:|:------------:|:--:|
| ...
- 101
2
votes
2
answers
221
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,
...
1
vote
1
answer
368
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 | ...
BJeni
1
vote
1
answer
761
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 ...
- 101
0
votes
1
answer
60
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,...
- 5
0
votes
1
answer
117
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 ...
- 101
0
votes
1
answer
35
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 ...
- 159
0
votes
1
answer
112
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 ...
- 23
0
votes
1
answer
88
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 ...
1
vote
1
answer
68
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 ...
- 27
1
vote
1
answer
114
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-...
- 113
1
vote
2
answers
113
views
Query for condition over time
Say we have a table setup like this:
+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
| 1 | 1 | ...
- 135
2
votes
1
answer
97
views
Gaps and islands with GPS breadcrumb data
I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life. Most of the GAI problems I've seen involve data in the form of:
[Id] [StartDateTime] [EndDateTime]
But mine in ...
- 165
0
votes
1
answer
702
views
GROUP BY with date ranges when values change but then change back
Consider a historical table where a value was changed and then changed back:
value | date
3 | 1/1/2019
3 | 1/2/2019
3 | 1/3/2019
4 | 1/4/2019
4 | 1/5/2019
3 | 1/6/2019
3 | ...
- 101
2
votes
1
answer
32
views
Add Gameweek Fixeture based on Datefield
having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in ...
- 33
-4
votes
1
answer
62
views
How to get the users who viewed a video on 95%?
There is a table with viewed chunks.
video_id | user_id | start_second | end_second
1 | 1 | 0 | 10
1 | 1 | 0 | 20
1 | 2 | 1 ...
- 93
2
votes
0
answers
163
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
...
- 21
4
votes
2
answers
222
views
Grouping rows in ranges
Let's say I have the following (contrived) table of monitoring data:
RowID DateStamp Success Error
----- ----------------- ------- -----------
1001 5/...
- 9,732
0
votes
1
answer
140
views
Count Consecutive Codes
I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following ...
- 101
0
votes
2
answers
905
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
1
answer
66
views
How to select blocks from a result by condition?
Example table:
master_id 1 created_at 22.02.1997
master_id 1 created_at 22.03.1997
master_id 1 created_at 22.04.1997
master_id 1 created_at 22.07.1997
master_id 1 created_at 22.08.1997
master_id 1 ...
- 3