Skip to content
Advertisement

sql that finds records within 3 days of a condition being met

I am trying to find all records that exist within a date range prior to an event occurring. In my table below, I want to pull all records that are 3 days or less from when the switch field changes from 0 to 1, ordered by date, partitioned by product. My solution does not work, it includes the first record when it should skip as it’s outside the 3 day window. I am scanning a table with millions of records, is there a way to reduce the complexity/cost while maintaining my desired results?

http://sqlfiddle.com/#!18/eebe7

my solution:

desired output, capturing records that occur within 3 days of a switch going from 0 to 1, for each product, ordered by date:

Advertisement

Answer

If I understand correctly, you can just use lead() twice:

Here is a db<>fiddle.

EDIT (based on comment):

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement