MySQL Integration Block

-- Retrieve recently active customers with contact details
SELECT customer_id, first_name, email, signup_date
FROM customers
WHERE status = 'active'
AND signup_date >= DATE_SUB(NOW(), INTERVAL 60 DAY)
ORDER BY signup_date DESC
LIMIT 100;
 
-- Aggregate employee data by department for salary analysis
SELECT
  department,
  COUNT(*) as employee_count,
  AVG(salary) as avg_salary,
  MAX(salary) as max_salary
FROM employees
WHERE active = true
GROUP BY department
HAVING COUNT(*) > 10
ORDER BY avg_salary DESC;

-- Insert a single order with customer and product details
INSERT INTO orders (customer_id, product_id, quantity, order_date, status)
VALUES (101, 202, 3, NOW(), 'pending');
 
-- Bulk insert user settings with conflict resolution
INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES
  (1, 'language', 'en'),
  (1, 'timezone', 'UTC'),
  (2, 'language', 'es')
ON DUPLICATE KEY UPDATE
  setting_value = VALUES(setting_value),
  updated_at = NOW();

-- Deactivate customers with no recent purchases
UPDATE customers
SET status = 'inactive', last_updated = NOW()
WHERE last_purchase < DATE_SUB(NOW(), INTERVAL 3 MONTH);
 
-- Prioritize orders for premium customers
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.priority = 'urgent'
WHERE c.membership = 'gold'
AND o.status = 'pending';

-- Delete expired user sessions
DELETE FROM sessions
WHERE expires_at < NOW()
AND last_active < DATE_SUB(NOW(), INTERVAL 48 HOUR)
LIMIT 500;
 
-- Delete cancelled orders and their items
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'cancelled'
AND o.created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- Analyze customer order history with sequence and totals
SELECT
  customer_id,
  order_date,
  total_amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as order_number,
  SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS UNBOUNDED PRECEDING) as cumulative_total,
  LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_order_amount
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
ORDER BY customer_id, order_date;

-- Generate a sales report for a date range
CALL GenerateSalesReport('2025-01-01', '2025-06-30', 'detailed');
 
-- Retrieve output parameters from the stored procedure
SELECT @total_sales, @order_count, @top_customer;

-- Summarize quarterly sales by product category
SELECT
  product_category,
  SUM(CASE WHEN MONTH(order_date) = 4 THEN total_amount ELSE 0 END) as apr_sales,
  SUM(CASE WHEN MONTH(order_date) = 5 THEN total_amount ELSE 0 END) as may_sales,
  SUM(CASE WHEN MONTH(order_date) = 6 THEN total_amount ELSE 0 END) as jun_sales,
  COUNT(DISTINCT customer_id) as unique_buyers
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE YEAR(order_date) = YEAR(NOW())
GROUP BY product_category
ORDER BY (apr_sales + may_sales + jun_sales) DESC;

-- Search products with relevance scoring
SELECT
  product_id,
  product_name,
  description,
  MATCH(product_name, description) AGAINST('smartphone accessories' IN NATURAL LANGUAGE MODE) as relevance
FROM products
WHERE MATCH(product_name, description) AGAINST('smartphone accessories' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 25;

-- Extract user details from JSON data
SELECT
  user_id,
  JSON_EXTRACT(user_data, '$.full_name') as full_name,
  JSON_EXTRACT(user_data, '$.settings.language') as language
FROM profiles
WHERE JSON_EXTRACT(user_data, '$.active') = true;
 
-- Update JSON fields for user activity
UPDATE profiles
SET user_data = JSON_SET(user_data, '$.last_access', NOW())
WHERE user_id = ?;

-- Calculate order totals with generated columns
SELECT
  order_id,
  subtotal,
  tax_rate,
  subtotal * tax_rate as tax_amount,
  subtotal + (subtotal * tax_rate) as total_with_tax
FROM orders
WHERE (subtotal + (subtotal * tax_rate)) > 200;

-- Retrieve historical inventory data for analysis
SELECT * FROM inventory
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-03-31'
WHERE category = 'electronics';

-- Use a specific index for date-based queries
SELECT * FROM transactions USE INDEX (idx_transaction_date)
WHERE transaction_date >= '2025-01-01';
 
-- Analyze query execution for optimization
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*)
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
GROUP BY customer_id;

-- Optimize pagination with key-based filtering
SELECT * FROM products
WHERE product_id > 5000
ORDER BY product_id
LIMIT 25;
 
-- Perform efficient bulk data aggregation
INSERT INTO analytics_temp
SELECT customer_id, SUM(total_amount) as total_spent
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY customer_id
HAVING SUM(total_amount) > 500;

-- Process a new order with inventory and audit updates
START TRANSACTION;
  INSERT INTO orders (customer_id, total_amount, status)
  VALUES (?, ?, 'pending');
 
  UPDATE inventory
  SET stock_quantity = stock_quantity - ?
  WHERE product_id = ?;
 
  INSERT INTO order_audit (order_id, action, timestamp)
  VALUES (LAST_INSERT_ID(), 'created', NOW());
COMMIT;

-- Manage account balance updates with rollback option
START TRANSACTION;
  SAVEPOINT before_transfer;
 
  UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
 
  IF (SELECT balance FROM accounts WHERE account_id = 1) < 0 THEN
    ROLLBACK TO SAVEPOINT before_transfer;
  END IF;
COMMIT;

-- Inspect query execution plan for performance issues
EXPLAIN FORMAT=JSON
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 60 DAY);
 
-- Check for query warnings
SHOW WARNINGS;
 
-- View table metadata for diagnostics
SHOW TABLE STATUS LIKE 'orders';