AI for MariaDB

SELECT DATE_FORMAT(cancelled_at, '%Y-%m') AS month,
       COUNT(*) AS churned,
       (SELECT COUNT(*) FROM customers c2
        WHERE c2.created_at <= LAST_DAY(c1.cancelled_at)) AS total,
       COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers c2
        WHERE c2.created_at <= LAST_DAY(c1.cancelled_at)) AS churn_rate
FROM customers c1
WHERE cancelled_at IS NOT NULL
GROUP BY month
ORDER BY month DESC;

SELECT p.name, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.price) AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.name
ORDER BY units_sold DESC
LIMIT 20;

SELECT c.name, COUNT(DISTINCT DATE_FORMAT(o.created_at, '%Y-%m')) AS active_months
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.name
HAVING active_months = MONTH(CURDATE())
ORDER BY c.name;