mysql - Mysql select data with sub-select from one table about user tracking：先下降，同月完成

Question

我有带有字段的表“活动”

id -campaign_id

user_id - 用户 ID

created_at - 广告系列创建日期

状态（拒绝或完成） - 活动状态（在版主审核后）

我必须计算首先被管理员拒绝的用户数量，另一列计算在下降后的同一个月内进行另一次活动并完成的用户数量。最后，计算当月的转化率

表格示例

id / user_id / created_at / status
1 / 1244 / 2020-01-01 / declined
2 / 1244 / 2020-01-20 / completed
3 / 1467 / 2020-01-02 / declined
4 / 1467 / 2020-02-01 / completed
5 / 3567 / 2020-02-02 / decline
6 / 3567 / 2020-02-04 / completed
7 / 5689 / 2020-02-27 / decline
8 / 3567 / 2020-02-28 / completed

预期结果按月列分组年、月、下降用户数量、下降后完成用户数量、转化

year / month / decline_users / complet_after_decl_users / conv.

2020 / 1 / 2 / 1 / 50

2020 / 2 / 2 / 1 / 50

如果用户在拒绝后完成了多于一个 - 我们必须在拒绝后仅计算第一个完成（导致不同的情况）

查询示例

SELECT
    year(c.created_at),
    month(c.created_at),
    count(distinct(c.user_id)),
    count(distinct(com.comp_user)),
    count(distinct(com.comp_user))/count(distinct(c.user_id)) * 100
FROM campaign c
JOIN 
    (SELECT
        c.id,
        c.user_id as 'comp_user',
        c.created_at as 'compe_date'
    FROM campaign c
    WHERE c.status = 'completed'
    ) com
    ON c.user_id = com.comp_user
WHERE c.status = 'declined'
AND com.compe_date > c.created_at
AND month(com.compe_date) = month(c.created_at)
AND year(com.compe_date) = year(c.created_at)
GROUP BY year(c.created_at),  month(c.created_at)

再试一次

select month(t1.created_at) month, t1.total_completed, t2.total_declined, (t1.total_completed/t2.total_declined)*100 as conversion 
from 
(
 Select c1.created_at, count(distinct c1.user_id) total_completed from campaign c1
 left Join 
 (
  select user_id, max(created_at) date_declined from campaign
  where status = 'declined'
  group by user_id
 ) c2
 on c2.user_id = c1.user_id

 left join
 (
  select user_id, max(created_at) date_completed 
  from campaign
  where status = 'completed'
  group by user_id
 ) c3
 on c3.user_id = c1.user_id

 where datediff(date_completed, date_declined) > 0
 and year(date_completed) = year(date_declined)
 and month(date_completed) = month(date_declined)
 and year(c1.created_at) = 2020
 group by month(c1.created_at)
) t1

join 
(
 Select created_at, count(distinct user_id) total_declined
 from campaign
 where status = 'declined'
  and year(created_at) = 2020
 group by month(created_at)
) t2

on year(t1.created_at) = year(t2.created_at)
and month(t1.created_at) = month(t2.created_at)

标签： mysqlselectwhere-clauseself-join