Query to identify high spending customers over variable time periods

Question

question

tonylodge asked Oct 28, '19

Query to identify high spending customers over variable time periods

Hi, sorry if this is a newbie question -

Simplifying our order table into CustomerId, OrderDate, OrderTotal

I've been asked to write a query to identify customers who have Sum(OrderTotal) > x in any 6 month period over the last 3 years. So for some customers they may exceed that Jan 25 '18 - Feb 25 '18, some could exceed it Dec 25 '18 - Apr 5 '19 etc.

I'm not a sql expert, am struggling, and have a rather tight deadline to get this done so I would be super grateful for any help with this query. Thank you.

Tony

query sql query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-10-29T13:56:44Z

Kev Riley answered Oct 29, '19

I think a lot of the implementation here is going to be driven by exactly how the data is stored, but hopefully this example will get you started in the right direction.

This is not the most efficient solution as it calculates the 6-monthly sales total for every given point in the base data.

In this example I use dateadd(month,6, OrderDate) to define a 6-month period, but you may need a more granular definition, i.e. 26 weeks or 182 days or whatever. Same for the definition of 'last 3 years' too.

declare @YourTable table (CustomerId int, OrderDate date, OrderTotal int)

insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 1, '1 Jan 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 1, '1 Feb 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 1, '1 Mar 2018', 15
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 2, '1 Jan 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 2, '1 Feb 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 2, '1 Aug 2018', 25
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 3, '1 Jan 2018', 100
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 4, '1 Jan 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 4, '1 Feb 2018', 10
insert into @YourTable (CustomerId, OrderDate, OrderTotal) select 4, '1 Sep 2018', 15

-- in this example the sales threshold is set at 30 over a period of 6 months

select
    YT1.CustomerId,
    (select sum(OrderTotal) from @YourTable YT2 
     where YT1.CustomerID = YT2.CustomerId 
     and YT2.OrderDate between YT1.OrderDate 
     and dateadd(month, 6, YT1.OrderDate) ) TotalSales6Monthly
from @YourTable YT1
where
     (select sum(OrderTotal) from @YourTable YT2 
     where YT1.CustomerID = YT2.CustomerId 
     and YT2.OrderDate between YT1.OrderDate 
     and dateadd(month, 6, YT1.OrderDate) ) >= 30  --the sales threshold
and 
	YT1.OrderDate > dateadd(year, -3, getdate()) --last 3 years



CustomerId  TotalSales6Monthly
----------- ------------------
1           35
2           35
3           100
(3 rows affected)

Customer ID 4 is not returned as the threshold of 30 is not exceed in a 6 month period

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tonylodge commented · Oct 29, 2019 at 04:04 PM

Wow, thanks so much Kev. As it stands I've just now had a huge workload added to my plate on top of this so was getting very stressed. This is a massive help.

Efficiency isn't too much of an issue as it will be run in an overnight process, which we are only using a fraction of our available time for.

The only changes I made were removing the totalsales6monthly, as I didn't need the values in the results - just the customers' Id, and popping a DISTINCT in there as it returns mutliple results when you have many orders.

Once again, many thanks. :)

Best wishes,

Tony

0 ·

question