自注册之日起1个月和2个月内，每位会员的平均收入

Question

我需要计算在1月份注册的会员在注册日期后1个月和2个月内的平均收入。

仅使用SELECT语句提供输出(没有临时表或函数(。也在寻找MYSQL语法

我有两张桌子。

Table: memberships, Columns:
member_id int PK
signup_date datetime
join_country varchar(2)
email varchar(45)
cancellation_date date
expiry_date date
Table: transactions, Columns:
transaction_id int PK
member_id int
transaction_date datetime
transaction_type_ID int
product_id int
transaction_amount float

SELECT m.member_id, m.signup_date, AVG(t.transaction_amount)
FROM memberships m
INNER JOIN transactions t
ON m.member_id = t.member_id
WHERE m.signup_date = '2018-01-01'
AND t.transaction_date >= '2018-01-01' AND t.transaction_date < '2018-03-01';

Answer 1

您只做1月1日注册的会员，而不是整个1月。同样，你计算的是从1月1日起的2个月，而不是他们的实际注册日期。

你不是在计算每个会员的平均值，而是在计算每个交易的平均值。您需要一个子查询来获得每个成员的总收入，然后主查询可以获得平均值。

您没有按月对收入进行分组，因此在注册后的1个月和2个月内不会产生单独的结果。

SELECT month, AVG(total) AS avg_revenue
FROM (
SELECT TIMESTAMPDIFF(month, m.signup_date, t.transaction_date)+1 AS month,
SUM(t.transaction_amount) AS total
FROM mindgeek.memberships m
INNER JOIN mindgeek.transactions t
ON m.member_id = t.member_id AND t.transaction_date < DATE_ADD(m.signup_date, INTERVAL 2 MONTH)
WHERE m.signup_date BETWEEN '2018-01-01' AND '2018-01-31'
GROUP BY m.member_id, month
) AS per_member
GROUP BY month

我将1添加到TIMESTAMPDIFF()的结果中，因为它返回了完整的月份数，四舍五入。因此，从注册到1个月后的时间段返回0。