Solved

PL/SQL count Recency in transaction by date SQL

Posted on 2008-10-03
11
1,103 Views
Last Modified: 2013-12-18
Hi guys,
thanks in advance for your help.
I want to be able to count customers recency or how offen they placed an order. e.g. if someone start with the first order on 1/15/07 then the second order may be 3/3/07, the third on 5/20/07 and so on. I want to find out the gap between orders. I have a total of 100,000 cust who placed their first order during Jan07 and the end date for this will be Jan08. Is there any way I can integrate this with PL/SQL or ever just SQL. here is a sample form a customer:

CUST_NUMBER      ORDERS      ORDER_DATE
137                            4                    1/1/2007
137                            2       1/4/2007
137                            1        6/26/2007
137                            1        7/14/2007
137                            3         7/20/2007
137                            1        9/24/2007
137                            4        12/29/2007
137                            1                     1/6/2008
137                            3        1/19/2008

thanks!

0
Comment
Question by:JoeSand2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22634472
And what output would you like to see for that sample?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22634600
You can start with SQL like in snippet and then if you want average order frequency, you can can group by columns you want and use AVG() on DaysSinceLastOrder column.
SELECT *, Case When PREV_ORDER_DATE IS NOT NULL Then DateDiff(dd, PREV_ORDER_DATE, ORDER_DATE) End As DaysSinceLastOrder
FROM (SELECT a.CUST_NUMBER, a.ORDERS, a.ORDER_DATE, 
(Select MIN(ORDER_DATE) FROM CustomerOrders b WHERE b.CUST_NUMBER = a.CUST_NUMBER AND b.ORDER_DATE < a.ORDERDATE ) As PREV_ORDER_DATE
FROM CustomerOrders a) AS derived

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22634617
Sorry, I started writing formula going the other way, so MIN is wrong here.  I changed to start recording on second order since that makes more sense from a frequency perspective, so you want the MAX order with date less than current order.
SELECT *
, Case When PREV_ORDER_DATE IS NOT NULL Then DateDiff(dd, PREV_ORDER_DATE, ORDER_DATE) End As DaysSinceLastOrder
FROM (SELECT a.CUST_NUMBER, a.ORDERS, a.ORDER_DATE, 
(Select MAX(ORDER_DATE) FROM CustomerOrders b 
WHERE b.CUST_NUMBER = a.CUST_NUMBER 
AND b.ORDER_DATE < a.ORDERDATE ) As PREV_ORDER_DATE
FROM CustomerOrders a) AS derived

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 27

Expert Comment

by:sujith80
ID: 22634640
Is this what you are looking for?
select cust_number, orders, order_date, 
nvl( (order_date - lag(order_date, 1) over(partition by cust_number order by order_date)), 0 ) gap
from <your table>;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22634691
FYI, my syntax may be more on SQL server side, so convert to equivalent syntax for Oracle for functions like DateDiff, but I believe they are the same.
0
 

Author Comment

by:JoeSand2005
ID: 22634708
Sorry guys, I just want to be more specific. In my sample I showed only one customer but the table actually has 100,000 diferents customers and the date goes from Jan07 to Jan08 and the output could be: I'm sure you may have a better idea of an output, please feel free to show the best output for this task, thanks so much!

Total Cust, Total Orders,  Gap_Between_Orders, Date_of_Next_Order

0
 
LVL 27

Accepted Solution

by:
sujith80 earned 150 total points
ID: 22634774
Try this:
select total_cust, total_orders, order_date, 
nvl( (order_date - lag(order_date, 1) over(order by order_date)), 0 ) gap_between_orders,
lead(order_date, 1) over(order by order_date) date_of_next_order
from (
select count(distinct cust_number) total_Cust, sum(orders) total_orders, order_date
from tbl1
group by order_date);

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 40 total points
ID: 22634930
Again, assuming SQL syntax is SQL syntax. :) Try applying aggregates to my query as I suggested.
SELECT COUNT(DISTINCT CUST_NUMBER) AS TotalCust
, SUM(ORDERS) AS TotalOrders
, AVG(Case When PREV_ORDER_DATE IS NOT NULL Then DateDiff(dd, PREV_ORDER_DATE, ORDER_DATE) End) As Gap_Between_Orders
, DateAdd(dd, AVG(Case When PREV_ORDER_DATE IS NOT NULL Then DateDiff(dd, PREV_ORDER_DATE, ORDER_DATE) End), MAX(ORDER_DATE)) AS Date_of_Next_Order
FROM (SELECT a.CUST_NUMBER, a.ORDERS, a.ORDER_DATE, 
(Select MAX(ORDER_DATE) FROM CustomerOrders b 
WHERE b.CUST_NUMBER = a.CUST_NUMBER 
AND b.ORDER_DATE < a.ORDERDATE ) As PREV_ORDER_DATE
FROM CustomerOrders a) AS derived

Open in new window

0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 30 total points
ID: 22649675
If the DateDiff and DateAdd stuff is throwing you off ... my understanding is that Oracle lacks such a function and uses straight subtraction and addition:
http://asktom.oracle.com/tkyte/Misc/DateDiff.html
http://www.psoug.org/reference/date_func.html


Adjusting mwvisa1's code for that, I think it would look like this:

SELECT COUNT(DISTINCT CUST_NUMBER) AS TotalCust
, SUM(ORDERS) AS TotalOrders
, AVG(Case When PREV_ORDER_DATE IS NOT NULL Then ORDER_DATE - PREV_ORDER_DATE End) As Gap_Between_Orders
,  AVG(Case When PREV_ORDER_DATE IS NOT NULL Then ORDER_DATE - PREV_ORDER_DATE End) + MAX(ORDER_DATE) AS Date_of_Next_Order
FROM (SELECT a.CUST_NUMBER, a.ORDERS, a.ORDER_DATE, 
(Select MAX(ORDER_DATE) FROM CustomerOrders b 
WHERE b.CUST_NUMBER = a.CUST_NUMBER 
AND b.ORDER_DATE < a.ORDERDATE ) As PREV_ORDER_DATE
FROM CustomerOrders a) AS derived

Open in new window

0
 
LVL 3

Assisted Solution

by:gajmp
gajmp earned 30 total points
ID: 22653044
SELECT CUST_NUMBER ,
SUM(ORDERS) OVER (PARTITION BY CUST_NUMBER  ORDER BY ORDER_DATE) TOT_ORDER,
(ORDER_DATE - LAG(ORDER_DATE) OVER (PARTITION BY CUST_NUMBER ORDER BY ORDER_DATE)) LAG,
LEAD(ORDER_DATE) OVER (PARTITION BY CUST_NUMBER ORDER BY ORDER_DATE) NEXT_ORDER
FROM CUST
0
 

Author Comment

by:JoeSand2005
ID: 22708162
Hey guys,
Sorry for taking so long.  I had trouble with the submit button. I have to go back to the original table layout and make correction. Bellow is the table I'm working on to get the gap between orders;
 
 
CUST_NUMBER     ORDER_#      ORDER_DATE               SALES
1374                           6611                1/1/2007                         $27.78
1374                           7712                1/16/2007                       $75.92
1374                           3018                5/10/2007                       $39.09
1374                           3674                6/14/2007                       $17.99
1374                           3675                7/8/2007                         $13.45
1374                           3741                7/8/2007                           $6.15
1375                           5512                1/7/2007                           $8.81
1375                           5535                1/7/2007                         $48.50
1375                           5621                1/15/2007                       $25.36
1375                           5320                1/20/2007                         $4.81
1375                           5340                2/10/2007                       $20.52

Now, I need to be able to get the gap between orders by customers if the order occurred within the same day then 0 gaps. Also I need to identify the initial order and the last order date so eventually I will get the avg gap between orders per customer for the full year, and the avg gap between orders per day. The output should look something like this; described below in the Code Snippet area.
 
sujith80: The LAG function you provided in your query is very helpful but I just can not get the avg gap between orders and the final avg for the full year between the inittial order date and the last order date.
Thanks so much for your patience  

Total_Cust   #_Orders     Avg_Gap_Days
2            0            3.5
3            1            5
4            2            4.5
3            3            5.5
10           4            6.5
12           5            2.5                     

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question