Compare values of different time-stamps and identify if they have changed

Question

DB-Fiddle

CREATE TABLE operations (
    id int auto_increment primary key,
    time_stamp DATE,
    product VARCHAR(255),
    plan_week VARCHAR(255)
);

INSERT INTO operations
(time_stamp, product, plan_week 
)
VALUES 
("2020-01-01", "Product_A", "CW01"),
("2020-01-01", "Product_B", "CW01"),
("2020-01-01", "Product_C", "CW01"),

("2020-03-15", "Product_A", "CW01"),
("2020-03-15", "Product_B", "CW02"),
("2020-03-15", "Product_C", "CW02"),
("2020-03-15", "Product_D", "CW01");

Expected Result

product          week_switch
Product_A           no
Product_B           yes
Product_C           yes
Product_D           no 

---

In the above result I want to check if a plan_week of a product has switched from one time_stamp to antoher time_stamp.
If the condition is met yes should be used as value. If not no should be inserted.

SELECT
product
FROM operations 
GROUP BY 1;

I have no clue what kind of query I need to achieve this. Do you have any idea?

Answer 1

It looks like you want:

select
    product,
    case when min(plan_week) <> max(plan_week) then 'yes' else 'no' end as week_switch
from operations
where time_stamp in ('2020-01-01', '2020-03-15')
group by product

This aggregates the rows by product. Then, the case expression checks if there are (at least) two different values in plan_week, and sets the flag accordingly.

The where clause is not strictly necessary. You can remove it, depending on whether you want to check two particular dates or the entire dataset.