sql - 具有订单日期范围的SQL客户订单查询

Question

我正在尝试获取一个订单报告，该报告不仅返回客户的订单总数，还返回另外三个字段，其中包含第一个和最后一个订单的日期以及第一个和最后一个订单之间的范围（以天为单位）

以下查询

SELECT customers.name, 
   customers.customer_id, 
   COUNT(orders.order_id) AS Orderscount 
   FROM customers 
   JOIN orders ON customers.customer_id = orders.customer_id 
   GROUP BY customers.name,
            customers.customer_id                       
         HAVING Orderscount >= 2  
         ORDER BY Orderscount DESC

返回名称，customer_id和订单总数的表。

问题是，如何才能添加到该查询中以同时获取first_order，last_order的日期以及两者之间的日期范围？

我正在使用SQLite。

Answer 1

使用MIN和MAX获取第一个和最后一个订单日期，并使用julianday()计算范围：

SELECT customers.name, 
   customers.customer_id, 
   COUNT(orders.order_id) AS Orderscount,
   MIN(col_name) AS firstOrder,
   MAX(col_name) AS lastOrder,
   ROUND(julianday(MAX(col_name)) - julianday(MIN(col_name))) AS range
FROM customers 
JOIN orders ON customers.customer_id = orders.customer_id 
GROUP BY customers.name,
         customers.customer_id                       
HAVING Orderscount >= 2  
ORDER BY Orderscount DESC