Back
Syntax
Study
Editor
Mode:
HTML
CSS
JavaScript
PHP
Reset
Run »
HTML / CSS / JS
DELIMITER // CREATE EVENT nightly_report ON SCHEDULE EVERY 1 DAY STARTS "2024-01-01 01:00:00" DO BEGIN TRUNCATE TABLE daily_report; INSERT INTO daily_report (date, new_users, orders, revenue, avg_order) SELECT CURDATE() - INTERVAL 1 DAY AS date, COUNT(DISTINCT u.id), COUNT(DISTINCT o.id), COALESCE(SUM(o.total), 0), COALESCE(AVG(o.total), 0) FROM users u LEFT JOIN orders o ON o.user_id = u.id AND DATE(o.created_at) = CURDATE() - INTERVAL 1 DAY WHERE DATE(u.created_at) = CURDATE() - INTERVAL 1 DAY; END// DELIMITER ;
Result
Open