Sql – Query to calculate no. of buyers monthly

i would really need your help in creating a query where i can show the number of buyers who made more than 1 order in a month for the period January 2017 to December 2017.

Also, the no. of these buyers that didn’t order again within the next 12 months.

Below is sample my data.

Date	CID (customer id)	OrderNo
2017-02-03 0:23	924832	000023149
2017-02-05 0:11	924162	000092384
2017-07-01 0:53	914861	000023182
2017-08-09 0:42	924832	000021219

Output should be like this

Month	Buyers with >1 order	Buyers that didnt order for the next 12M
2017-01-01	122	92
2017-02-01	74	24
2017-03-01	216	107

Answer

This should to match your description:

with cte as
 (
   select
      ca.ym
     ,cid
     -- flag buyers who made more than 1 order in a month
     ,case when count(*) > 1 then 1 else 0 end as multi_buy

     -- flag buyers that didn't order again within the next 12 months
     ,case when LEAD(ym,1,ym) -- month of next order
                over (partition by CID
                      order by ym) < DATEADD(month, 13, ym)
           then 0 
           else 1
      end as no_buy_within_range
   from orders
   CROSS APPLY 
    ( -- truncate the OrderDate to the 1st of month
      SELECT convert(Date, DATEADD(month, DATEDIFF(month, 0, OrderDate), 0)) as ym
    ) as ca
   group by
      ym
     ,cid
 )
select 
   ym
  ,sum(multi_buy)
  ,sum(no_buy_within_range)
from cte
group by ym
order by ym