Solved

PL/SQL count Recency in transaction by date SQL

Posted on 2008-10-03
11
1,090 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

752 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