问题:
现在数据库中有一张用户交易表order,其中有userid(用户ID)、orderid(订单ID)、amount(订单金额)、paytime(支付时间),请写出对应的SQL语句,查出每个月的新客数(新客指在严选首次支付的用户),当月有复购的新客数,新客当月复购率(公式=当月有复购的新客数/月总新客数)。
涉及如何用多个SELECT完成不同条件的查询,并通过嵌套利用查询结果进行计算
SELECT new.date AS date, new.new_numb AS new_numb, re. re_numb AS re_numb, new.new_numb/re.re_numb AS re_percent
FROM
(
SELECT date, count(new_user) AS new_numb
FROM
(
SELECT DISTINCE userid AS new_user, date(paytime) AS date
FROM order
ORDER BY date(paytime)
)
GROUP BY date
) AS new,
(
SELECT count(userid) AS re_numb, date(type) AS date
FROM order
GROUP BY userid, date(type)
) AS re
WHERE new.date = re.date