I have a query below that is working but I am having some issues, which I hope someone could help me with.
First, I want to do a JOIN to include the customers first and last name in the output.
Second, I want to include customer_id 5, which has no data and those columns should be NULL.
Lastly, I want to include the NUMBER of elapsed days since a customers last purchase.
Below is my test CASE and attempt. Any help would be greatly appreciated.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Aaron' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Mazzarone' FROM DUAL UNION ALL
SELECT 4, 'Joseph', 'Zanzone' FROM DUAL UNION ALL
SELECT 5, 'Sandy', 'Herring' FROM DUAL;
ALTER TABLE customers
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE items
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
ALTER TABLE items
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);
create table purchases(
ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
customer_id number,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
purchase_date timestamp
);
ALTER TABLE purchases
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);
ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);
insert into purchases (customer_id, product_id, quantity, purchase_date)
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 15 UNION ALL
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
connect by level <= 23
union all
select 3, 100,1, date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
connect by level <= 60;
SELECT
customer_id,
/*
q.first_name,
q.last_name,
*/
Num_Orders,
purchase_date AS Last_Purchase_Date,
order_id AS Last_Order_ID
FROM (
SELECT p.*,
COUNT( order_id ) OVER ( PARTITION BY customer_id ) AS Num_Orders,
ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY purchase_date DESC,
order_id DESC ) AS rn
FROM purchases p
) q
/*
join purchases p on q.customer_id = p.customer_id
*/
WHERE rn = 1
ORDER BY customer_id;
One option is to
systimestamp and extract number of days out of itSQL> with last_purchase_per_customer as
2 (select p.customer_id, max(p.purchase_date) max_purchase_date
3 from purchases p
4 group by p.customer_id
5 )
6 select c.customer_id, c.first_name, c.last_name, p.max_purchase_date,
7 extract(day from (systimestamp - p.max_purchase_date)) as number_of_days
8 from customers c left join last_purchase_per_customer p on p.customer_id = c.customer_id
9 order by c.customer_id;
CUSTOMER_ID FIRST_N LAST_NAME MAX_PURCHASE_DATE NUMBER_OF_DAYS
----------- ------- --------- ---------------------------------- --------------
1 Faith Aaron 22-APR-2023 00:00:00.000000 105
2 Lisa Saladino 24-APR-2023 11:00:00.000000 103
3 Micheal Mazzarone 14-MAY-2023 01:00:00.000000 83
4 Joseph Zanzone 13-JAN-2023 12:00:00.000000 204
5 Sandy Herring
SQL>
[EDIT] If you want to get any number of last transactions, modify CTE to rank them per customer by purchase date in descending order, and then - in main query - fetch only desired number of transactions. There's additional where condition (or p.rnk is null) which handles customers who didn't purchase anything.
SQL> with purchases_per_customer as
2 (select p.customer_id,
3 p.purchase_date,
4 rank() over (partition by p.customer_id order by p.purchase_date desc) rnk
5 from purchases p
6 )
7 select c.customer_id, c.first_name, c.last_name, p.purchase_date,
8 extract(day from (systimestamp - p.purchase_date)) as number_of_days
9 from customers c left join purchases_per_customer p on p.customer_id = c.customer_id
10 where p.rnk <= &par_number_of_transactions
11 or p.rnk is null
12 order by c.customer_id, p.purchase_date desc;
Enter value for par_number_of_transactions: 3
CUSTOMER_ID FIRST_NAME LAST_NAME PURCHASE_DATE NUMBER_OF_DAYS
----------- ----------- --------- ----------------------------------- --------------
1 Faith Aaron 22-APR-2023 00:00:00.000000 106
1 Faith Aaron 20-APR-2023 00:00:00.000000 108
1 Faith Aaron 18-APR-2023 00:00:00.000000 110
2 Lisa Saladino 24-APR-2023 11:00:00.000000 104
2 Lisa Saladino 23-APR-2023 10:00:00.000000 105
2 Lisa Saladino 22-APR-2023 09:00:00.000000 106
3 Micheal Mazzarone 14-MAY-2023 01:00:00.000000 84
3 Micheal Mazzarone 13-MAY-2023 02:00:00.000000 85
3 Micheal Mazzarone 12-MAY-2023 03:00:00.000000 86
4 Joseph Zanzone 13-JAN-2023 12:00:00.000000 205
4 Joseph Zanzone 13-JAN-2023 07:00:00.000000 205
4 Joseph Zanzone 13-JAN-2023 02:00:00.000000 205
5 Sandy Herring
13 rows selected.
SQL>