step1: 筛选大会员开始生效日期在’2021-9-1‘和'2021-9-30'日之间的所有用户；

step2: 增加新的列seg_date，根据开始生效日期将seg_date对应的数据标记为“上旬”，“中旬”，“下旬”；

step3: 分组统计上中下寻对应的数据的个数。

SELECT seg_date, count(*) as cnt

FROM(

SELECT *,

CASE

WHEN begin_date BETWEEN '2021-9-1' and '2021-9-10' THEN '上旬'

WHEN begin_date BETWEEN '2021-9-11' and '2021-9-20' THEN '中旬'

WHEN begin_date BETWEEN '2021-9-21' and '2021-9-30' THEN '下旬'

END as seg_date

FROM detail_list_tb

WHERE begin_date BETWEEN '2021-9-1' and '2021-9-30'

)t

group by seg_date;