Skip to content
Advertisement

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

Advertisement

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
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement