SQL 查询过去一年每个月销售额、卖出商品数和新增用户数

查询过去一年每个月的销售额：

SELECT 
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(total) AS sales_amount
FROM 
    orders
WHERE 
    created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    month;

查询过去一年每个月的卖出商品数：

SELECT 
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    SUM(oi.quantity) AS sold_items
FROM 
    orders o
INNER JOIN 
    order_items oi ON o.id = oi.order_id
WHERE 
    o.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    month;

查询过去一年每个月的新增用户数：

SELECT 
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS new_users
FROM 
    users
WHERE 
    created_at BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
GROUP BY 
    month;