SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE recipients ( id INT, email varchar(100), shop varchar (20), first_notification_time datetime, second_notification_time datetime, third_notification_time datetime ); INSERT INTO recipients (id,email, shop, first_notification_time, second_notification_time, third_notification_time) VALUES (1,'aaa@aaa.com', 'vivantis', '2024-07-16 16:00:00', '2024-07-16 14:00:00', '2024-07-16 10:00:00'); INSERT INTO recipients (id,email, shop, first_notification_time, second_notification_time, third_notification_time) VALUES (2,'aaa@aaa.com', 'hodinky', '2024-07-16 16:00:00', '2024-07-16 14:00:00', '2024-07-16 10:00:00'); INSERT INTO recipients (id,email, first_notification_time, second_notification_time, third_notification_time) VALUES (3,'aab@aaa.com', '2024-07-16 16:00:00', '2024-07-16 14:00:00', null); SELECT email FROM ( SELECT email, SUM(CASE WHEN first_notification_time >= NOW() - INTERVAL 1 DAY THEN 1 ELSE 0 END + CASE WHEN second_notification_time >= NOW() - INTERVAL 1 DAY THEN 1 ELSE 0 END + CASE WHEN third_notification_time >= NOW() - INTERVAL 1 DAY THEN 1 ELSE 0 END) as email_count FROM recipients WHERE AND shop = 'vivantis' GROUP BY email ) AS subquery WHERE email_count >= 3