In Oracle SQL my goal is to receive the total number of orders made by a customer in the last 10 days but shown by the order id and not the customer id.

The following situation is given:

My desired outcome is:

How can I facilitate this in the most efficient way?

CodePudding user response：

You can try to use self-join with date of subtraction condition, the condition need to judge what rows are during 10 days

Query 1:

SELECT t1.Customer_ID,
       t1.Order_NR,
       COUNT(*) Number_Orders
FROM T t1 
INNER JOIN T t2
ON t1.Customer_ID = t2.Customer_ID AND t2.Order_Date BETWEEN t1.Order_Date - 10 AND t1.Order_Date
GROUP BY t1.Customer_ID,
       t1.Order_NR
ORDER BY t1.Order_NR

Results:

| CUSTOMER_ID | ORDER_NR | NUMBER_ORDERS |
|-------------|----------|---------------|
|         100 |        1 |             1 |
|         200 |        2 |             1 |
|         300 |        3 |             1 |
|         100 |        4 |             2 |
|         100 |        5 |             2 |
|         200 |        6 |             1 |
|         700 |        7 |             1 |
|         800 |        8 |             2 |
|         800 |        9 |             2 |
|         800 |       10 |             3 |

CodePudding user response：

You do not need a self-join and can simply use the COUNT analytic function with a RANGE window:

SELECT customer_id,
       order_no,
       order_date,
       COUNT(*) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         RANGE BETWEEN INTERVAL '10' DAY PRECEDING AND INTERVAL '0' DAY FOLLOWING
       ) AS numer_orders
FROM   table_name
ORDER BY order_no

Which, for the sample data:

CREATE TABLE table_name (customer_id, order_no, order_date) AS
SELECT 100,  1, DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 200,  2, DATE '2021-01-02' FROM DUAL UNION ALL
SELECT 300,  3, DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 100,  4, DATE '2021-01-09' FROM DUAL UNION ALL
SELECT 100,  5, DATE '2021-01-15' FROM DUAL UNION ALL
SELECT 200,  6, DATE '2021-01-18' FROM DUAL UNION ALL
SELECT 700,  7, DATE '2021-01-20' FROM DUAL UNION ALL
SELECT 800,  8, DATE '2021-01-25' FROM DUAL UNION ALL
SELECT 800,  9, DATE '2021-01-25' FROM DUAL UNION ALL
SELECT 800, 10, DATE '2021-01-28' FROM DUAL;

Outputs:

CUSTOMER_ID	ORDER_NO	ORDER_DATE	NUMER_ORDERS
100	1	01-JAN-21	1
200	2	02-JAN-21	1
300	3	05-JAN-21	1
100	4	09-JAN-21	2
100	5	15-JAN-21	2
200	6	18-JAN-21	1
700	7	20-JAN-21	1
800	8	25-JAN-21	2
800	9	25-JAN-21	2
800	10	28-JAN-21	3

db<>fiddle here