第一次和第二次购买之间的平均天数

tkclm6bt 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(343)

有人能帮我做以下事情吗。我需要计算客户第一次和第二次购买的平均日差：
我有以下几点：有人能帮助我的问题吗。

SELECT src.id, AVG(DATEDIFF(dest.purchasing, src.registrations)) AS avgdays FROM 
(SELECT accounts.id, accounts.`created_date` AS registrations FROM accounts
WHERE YEAR(accounts.`created_date`) =2018 AND MONTH(accounts.`created_date`) =4) src
INNER JOIN
(SELECT account_id, MIN(created_date) AS purchasing
FROM ordering 
WHERE STATUS = 'Fulfilled'
GROUP BY account_id
) dest
ON 
dest.account_id = src.id;

mysql

来源：https://stackoverflow.com/questions/50735856/average-days-between-first-and-second-purchase

1条答案

按热度按时间

thtygnil1#

如果客户注册日期也是他的第一个订单购买日期，下面的查询将为您提供第二次购买之间的平均差异。

select account_id,customer_created, 
avg(DATEDIFF(order_created, customer_created))  from
(select a.account_id,b.created_at as order_created, a.created_at 
customer_created from
(select * from accounts where YEAR(created_at) =2018 AND 
MONTH(created_at) =4) a
INNER JOIN ordering b on a.account_id=b.account_id where 
b.status="Delivered" ) a 
where customer_created <> order_created  GROUP BY a.account_id ;

赞(0）回复(0）举报 2021-06-20

我来回答

第一次和第二次购买之间的平均天数

1条答案

相关问题

热门标签

最新问答