主页 / 问题 / 1048958
Accepted
Ayo
Asked:2020-11-21 21:45:26 +0000 UTC2020-11-21 21:45:26 +0000 UTC

来自同类群组的月度和年度收入汇总

  • 772

有2张桌子：

  • users带字段：
    • user_id,
    • reg_date,
    • country
  • purchases带字段：
    • id（付款ID），
    • amount（支付金额），
    • user_id,
    • created（付款日期）

有必要补充查询，以便我们得到 2 列“30d 收入”和“360d 收入”而不是收入列 - 来自同期群的月份和年度收入。但是有了日期，我完全被困住了。

这是我写了一半的查询：

select date_format(u.reg_date, '%Y-%m') as 'cohort',
       u.country,
       count(u.user_id) as 'new_users',
       sum(p.amount) as 'revenue'
from users u
left join purchases p on u.user_id = p.user_id
where date(u.reg_date) >= '2019-01-01'
group by date_format(u.reg_date, '%Y-%m'), country
having revenue > 0

*这里使用left join是为了不丢失用户数，因为 考虑到那些不付款的人，有必要计算他们的 ARPU。

mysql

1 个回答

  1. Best Answer

    更新。提出了以下要求：

    select date_format(u.reg_date, '%Y-%m') as 'cohort',
       u.country,
       count(u.user_id) as 'new_users',
       sum(if(date(d.created) < date_add(date(u.reg_date), interval 1 month), amount, 0)) as '30d revenue',
       sum(if(date(d.created) < date_add(date(u.reg_date), interval 1 year), amount, 0)) as '360d revenue'
from users u
left join purchases p on u.user_id = p.user_id
where date(u.reg_date) >= '2019-01-01'
group by date_format(u.reg_date, '%Y-%m'), country
having revenue > 0

    一切正常，谢谢@teran

    • 0

相关问题