Mysql 从一张关于用户排序的表中选择带有子选择的数据：第一次下降，在同一个月完成后

问：

我有带有字段的表“campaign”

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)