Solved

PL/SQL count Recency in transaction by date SQL

Posted on 2008-10-03
11
1,063 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
  • 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 59

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 59

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
 
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 59

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 59

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now