Linked Questions

2 votes
1 answer
859 views

How to get max sequence of rows? [duplicate]

I have a table with statistic of games which has next rows (I only write about rows that has interest in this context): id BIGINT PRIMARY KEY date TIMESTAMP WITH TIME ZONE status ...
Yurii Vasilchuk's user avatar
19 votes
7 answers
5k views

Form groups of consecutive rows with same value

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp , id_type integer ) ; INSERT INTO tmp (date, id_type) ...
Lelo's user avatar
  • 193
20 votes
6 answers
34k views

Find "n" consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
boobiq's user avatar
  • 807
20 votes
7 answers
12k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
Stew's user avatar
  • 483
9 votes
2 answers
18k views

Generate a series of dates for each group in a table

I have a balances table in PostgreSQL 9.3 that looks like this: CREATE TABLE balances ( user_id INT , balance INT , as_of_date DATE ); INSERT INTO balances (user_id, balance, as_of_date) VALUES (...
Shaun Scovil's user avatar
6 votes
2 answers
27k views

How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
alexanderadam's user avatar
3 votes
3 answers
6k views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
Grace's user avatar
  • 31
2 votes
2 answers
2k views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
Evan Carroll's user avatar
  • 63.7k
3 votes
3 answers
2k views

Count number of occurences per location per hour in PostgreSQL

I have a dataset in Postgres of boat locations on waterways. Here is a sample of the table: boat_id ts waterway_id Boat_A 2019-01-01 16:29:11 WW_01 Boat_A 2019-01-01 17:03:04 WW_02 Boat_B 2019-01-...
Thomas's user avatar
  • 33
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
6 votes
1 answer
989 views

GROUP BY possible sequences

I have a table containing the following data, using Postgres 9.6: log_id | sequence | made_at (timestamp) 206480 1 1 206480 1 2 206480 2 3 206480 3 ...
DanFritz's user avatar
  • 195
3 votes
1 answer
3k views

Row number with reset in PostgreSQL

I have this data set in PostgreSQL: User_id | date | is_cleared | ---------------+-------------------------+------------+ C | 2016-08-03 18:28:38.37 | ...
Kugutsumen's user avatar
2 votes
1 answer
936 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
5 votes
1 answer
502 views

Transformation of row data based on preceding row for special cumulative sum

Following the answer to this question, I have managed to produce the following output to get a running sum of values: id creation operation value running sum SyJw-...
Yaneeve's user avatar
  • 177
3 votes
1 answer
2k views

Query with dynamic ranges in PostgreSQL based on status

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
Oscar Calderon's user avatar

15 30 50 per page