-1

I am working with a table containing the purchase history for a shop. There is a purchase id, a date column and a customer id. I am trying (without much success so far) to do two things:

Add a column which for each purchase tells how many purchases the customer made before this (in the last month). I started by joining the table on itself but haven't got much further. I know I'll need to somehow filter the date so it only counts purchases before this date and not more than a month ago. Any suggestions on a simple way to tackle this?

The second thing I would like to see is what the weekly rate of returning customer transactions is. That is, what proportion of the purchases are by someone who purchased recently (in the last month). Ideally I would be able to graph this so from my sql queries I would like to end up with a date, weekly total (the 7 days up to the date) and weekly rate. I have been reading up on rolling windows and to be honest am having a bit of trouble getting my head around it. My SQL level is still quite low unfortunately. Any tips on a relatively simple way to do this would be much appreciated. Thanks

R_Mor88
  • 1
  • 2
  • 2
    Too broad. Try reading this: https://stackoverflow.com/help/how-to-ask – mikeb Nov 07 '17 at 19:41
  • Adding a column likely isn't needed, calculate the purchases made prior on the fly. What you are asking for is more than possible, will need a few sub queries. Break your question down into parts, share the schema you have and the SQL you've attempted. Stack will take it from there...posts like this will get closed however. – Twelfth Nov 07 '17 at 19:49

1 Answers1

0

I would need to see your data structure for the table(s) to better answer your question. But right off the top of my head is seems like you just need a simple SELECT COUNT.

So something like this would return all transactions from a single customer made in the past month:

SELECT COUNT(purchase_id)
FROM purchases
WHERE customer_id='some_customer_id'
AND date >= DATEADD(m, -1, GETDATE());

As for your second question you would probably want to setup a job (jenkins, ect..) that would run a query every month. The results of which you would plot. Checkout https://oss.oetiker.ch/rrdtool/ for graphing

JustnW
  • 1