abbychau/mysql-parser demo

SELECT u.name, u.email, o.total, p.name as product_name, c.name as category FROM users u JOIN orders o ON u.id = o.user_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id JOIN categories c ON p.category_id = c.id WHERE o.total > 500 AND c.name = 'Electronics'

SELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salary, MAX(hire_date) as latest_hire, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_count FROM employees e LEFT JOIN departments d ON e.dept_id = d.id WHERE hire_date >= '2020-01-01' GROUP BY department, d.location HAVING COUNT(*) > 5 AND AVG(salary) > 50000 ORDER BY avg_salary DESC, employee_count DESC

SELECT customer_id, order_date, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as order_rank, LAG(total) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_total, running_total FROM ( SELECT customer_id, order_date, total, SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total FROM orders WHERE order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR) ) ranked_orders WHERE order_rank <= 10

CREATE TABLE user_profiles ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, birth_date DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, profile_data JSON, status ENUM('active', 'inactive', 'suspended') DEFAULT 'active', FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_email (email), INDEX idx_status_created (status, created_at) )

WITH RECURSIVE employee_hierarchy AS ( -- Base case: top-level managers SELECT emp_id, name, manager_id, department, salary, 1 as level, CAST(name AS CHAR(500)) as path FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive case: subordinates SELECT e.emp_id, e.name, e.manager_id, e.department, e.salary, eh.level + 1, CONCAT(eh.path, ' -> ', e.name) FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.emp_id WHERE eh.level < 5 ) SELECT emp_id, name, department, salary, level, path FROM employee_hierarchy ORDER BY level, department, name

SELECT product_category, month_year, total_sales, total_quantity, avg_price, RANK() OVER (PARTITION BY month_year ORDER BY total_sales DESC) as sales_rank, PERCENT_RANK() OVER (PARTITION BY product_category ORDER BY total_sales) as percentile_rank, total_sales - LAG(total_sales) OVER (PARTITION BY product_category ORDER BY month_year) as mom_growth, CASE WHEN total_sales > AVG(total_sales) OVER (PARTITION BY product_category) THEN 'Above Average' ELSE 'Below Average' END as performance FROM ( SELECT c.name as product_category, DATE_FORMAT(o.order_date, '%Y-%m') as month_year, SUM(oi.quantity * oi.price) as total_sales, SUM(oi.quantity) as total_quantity, AVG(oi.price) as avg_price FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN categories c ON p.category_id = c.id JOIN orders o ON oi.order_id = o.id WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 2 YEAR) GROUP BY c.name, DATE_FORMAT(o.order_date, '%Y-%m') ) monthly_stats ORDER BY product_category, month_year

INSERT INTO user_activity_log ( user_id, activity_type, activity_data, ip_address, user_agent, created_at ) SELECT u.id as user_id, 'bulk_update' as activity_type, JSON_OBJECT( 'updated_fields', JSON_ARRAY('email', 'last_login'), 'old_email', u.email, 'new_email', CONCAT('updated_', u.id, '@example.com') ) as activity_data, '192.168.1.1' as ip_address, 'System Migration Tool' as user_agent, NOW() as created_at FROM users u WHERE u.last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH) AND u.status = 'active' AND u.email NOT LIKE 'updated_%'

UPDATE products p JOIN ( SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count, SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) / COUNT(*) * 100 as positive_percentage FROM product_reviews WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP BY product_id HAVING COUNT(*) >= 10 ) review_stats ON p.id = review_stats.product_id SET p.avg_rating = review_stats.avg_rating, p.review_count = review_stats.review_count, p.rating_updated_at = NOW(), p.featured = CASE WHEN review_stats.positive_percentage > 80 AND review_stats.avg_rating > 4.0 THEN 1 ELSE 0 END WHERE p.status = 'active'