# Tag: gaps-and-islands

## Calculate interval between boolean column change

I have a table with measurements of weather here is a simplified version of it: and I need to calculate how much time is been sunny. I know I need to use window functions but I’m stuck. Because I need this interval to be calculated in some range for example for last day. I have been able to make a

## Number based on condition

I’m trying to generate a number based on a condition. When there is yes in column ‘Stop’ in the partition of a Client ordered by Start_Date, the Dense Rank has to start over. So I tried several things but it’s stil not what I want. My table with current number and expected number The query I used so far: This

## SQL to find start and end date for an ID

I have a table with ID, start_date and end_date columns. Table: I want to write a query to get the following output: Output: Answer You can do a cumulative sum to solve this variation of the gaps-and-island problem: Demo on DB Fiddle: ID | START_DATE | END_DATE -: | :——— | :——— 1 | 01/01/2017 | 01/01/2020 2 | 01/01/2016

## Row lumping, cycle dates

I want to look at the lead type and if that type is the same for that row then merge in those dates to fit within one row. I have the below table: Expected Results: How can I get my output to look like the expected results? I have tested withlead lag rank and case expression but nothing worthy of

## Find all rows in between a set in PostgreSQL

I have a table named tc_fuel that is receiving all the fuel related data from GPS vehicles, and I want to get the last “Tank Usage” to calculate MPG for a whole tank, but the readings when the tank is …

## Gaps and Islands Months only

I am working with a data set of clients and their date records. I am trying to apply a gaps and island problem only using MONTHS,( currently var char ‘YYYYMM’). I need to take individual records and …

## Retrieve Records of the Last Consecutive Dates in PostgreSQL

Edit Sorry for being unclear in the first post. If the item appears only once, it should also be included. Please, check updated sample table… I have a table of record of sales of each item per day….

## Find the longest streak of perfect scores per player

I have a the following result from a SELECT query with ORDER BY player_id ASC, time ASC in PostgreSQL database: I’m trying to find each player’s longest streak where points = 100, with the tiebreaker being whichever streak began most recently. I also need to determine the time at which that player’s longest streak began. The expected result would be:

## Gaps And Islands: Splitting Islands Based On External Table

My scenario started off similar to a Island and Gaps problem, where I needed to find consecutive days of work. My current SQL query answers “ProductA was produced at LocationA from DateA through DateB,…

## Oracle: Analytical functions Sub totals after each change in value

I have the following data (order of records as in the example): A B 1 10 1 20 1 30 1 40 2 50 2 65 2 75 1 89 1 100 from SQL: with x as ( select A, B from ( select 1 as A, …