PL/SQL count Recency in transaction by date SQL

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!

JoeSand2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
And what output would you like to see for that sample?
0
Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
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
Kevin CrossChief Technology OfficerCommented:
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
JoeSand2005Author Commented:
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
SujithData ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
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
Daniel WilsonCommented:
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
gajmpCommented:
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
JoeSand2005Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.