SQL Days since last unavailable

0
votes

I have a table (redshift db) with the following sample:

product_id | date    | is_unavailable 
1          | 1st Jan | 1              
1          | 2nd Jan | 0              
1          | 3rd Jan | 0              
1          | 4rd Jan | 1

Here , a combination of date and product_id is unique. I need to have a 4th column: "Days since last unavailable".

Here is the output required:

product_id | date    | is_unavailable | days_since_last_unavailable
    1      | 1st Jan | 1              |          -            
    1      | 2nd Jan | 0              |          1          
    1      | 3rd Jan | 0              |          2           
    1      | 4rd Jan | 1              |          0

I thought of using lag window function with partition over product_id , however, an additional condition of unavailable_flag has to be checked here which I cannot accommodate in my query.

select *, date-lag(date) over (partition by product_id order by date) as days_since_last_unavailbale from mytable order by product_id

However, I can't figure out how to use unavailable_flag since it is required to find the last date with unavailable_flag=1

2
sqlamazon-redshift
Unclear. Please tag with the database you are using, include your query, along with sample input/output data. – Tim Biegeleisen
Could you show the query you got so far? Please show example data of your table and your expected output. – MBijen
The question explicitly specifies Redshift, so I swapped the tag. – Gordon Linoff

2 Answers

1
votes

No LAG, but a simple MAX over a CASE:

max(case when is_unavailable = 1 then date end) -- previous unavailable date
over (partition by product_id
      order by date
      rows unbounded preceding)
0
votes

try this:

    create table #tmp (product_id INT,[date] DATETIME ,is_unavailable BIT)

    INSERT INTO #tmp
    SELECT 1,'2018-01-01',1
    union
    SELECT 1,'2018-01-02',0
    union
    SELECT 1,'2018-01-03',0
    union
    SELECT 1,'2018-01-04',1


    select product_id, date ,is_unavailable,
        DATEDIFF(d,
                CASE WHEN is_unavailable = 1 THEN date
                ELSE
                    MIN(case when is_unavailable = 1 then date end) over (partition by product_id) END,
                date) as days_sice_last_unavailable 

    FROM #tmp

    drop table #tmp