Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

PL/SQL count Recency in transaction by date SQL

Posted on 2008-10-03
Medium Priority
1,123 Views
Hi guys,
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
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
• 4
• 2
• 2
• +2

LVL 32

Expert Comment

ID: 22634472
And what output would you like to see for that sample?
0

LVL 60

Expert Comment

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
``````
0

LVL 60

Expert Comment

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
``````
0

LVL 27

Expert Comment

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
``````
0

LVL 60

Expert Comment

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

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

sujith80 earned 450 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);
``````
0

LVL 60

Assisted Solution

Kevin Cross earned 120 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
``````
0

LVL 32

Assisted Solution

Daniel Wilson earned 90 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://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
``````
0

LVL 3

Assisted Solution

gajmp earned 90 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

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
``````
0

Featured Post

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. â€¦
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
Course of the Month8 days, 20 hours left to enroll