I have table that looks like the following, with many emails as well. The column product can contain NULL or No values.

My intent is to assign to the NULL or No products the previous values of the product column that are not NULL or No. Like:

I have tried using WINDOWS functions like

SELECT email,
    date,
    product,
    CASE    
        WHEN product='No' THEN lag(product) OVER(PARTITION BY email ORDER BY date)
        WHEN product IS NULL THEN lag(product) OVER(PARTITION BY email ORDER BY date)
    END AS product2
    FROM table;

Obtaining

CodePudding user response：

You may use a subquery as the following:

SELECT T.email, T.date, 
   (SELECT D.product FROM table_name D 
    WHERE D.email=T.email AND D.date<=T.date AND D.product IS NOT NULL AND D.product<>'No' 
    ORDER BY D.date DESC 
    LIMIT 1) product
FROM table_name T
ORDER BY T.email, T.date

See a demo.

CodePudding user response：

What you did is actually correct. I'd change it slightly though:

SELECT
    email,
    date,
    CASE WHEN product = 'No'
        OR product IS NULL THEN
        lag(product) OVER (PARTITION BY email ORDER BY date)
    ELSE
        product
    END AS product
FROM
    table_name;