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.
166 questions
-1 votes
1 answer
151 views
How to sum values within blocks with Postgres?
Given this table called prices: my_date my_value my_separator 2012-02-10 100 1 2012-02-11 120 1 2012-02-12 120 1 2012-02-13 110 1 2012-02-14 100 0 2012-02-15 115 0 2012-02-16 130 1 2012-02-17 190 1 ...
- 1,203
0 votes
1 answer
139 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 ...
- 3
0 votes
2 answers
3k 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 (...
- 111
1 vote
1 answer
73 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/2022 19:...
- 35
0 votes
1 answer
228 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 ...
- 101
1 vote
1 answer
389 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 NULL 5 ...
- 13
1 vote
1 answer
51 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 ...
- 33
0 votes
1 answer
3k 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 | ...
0 votes
1 answer
445 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 ...
- 255
-3 votes
2 answers
110 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 query is desirable
2 votes
3 answers
431 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 (...
- 23
2 votes
1 answer
72 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 ...
- 35
1 vote
1 answer
50 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
255 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
713 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",&...
- 125