I have the following setup and I'm trying to get the earliest order_date for each customer but my CTE is giving me a syntax error, which I can't seem to resolve. Any help would be greatly appreciated.
CREATE TABLE customers(customer_id, customer_name, join_date) AS
SELECT 1, 'ABC', DATE '2020-05-20' FROM DUAL UNION ALL
SELECT 2, 'XYZ', DATE '2020-04-03' FROM DUAL;
CREATE TABLE products(product_id, product_name) AS
SELECT 1, 'Coca Cola' FROM DUAL UNION ALL
SELECT 1, 'Dr. Pepper' FROM DUAL UNION ALL
SELECT 3, 'Pepsi' FROM DUAL;
CREATE TABLE sales(customer_id, product_id, order_date) AS
SELECT 1, 1, DATE '2021-11-20' FROM DUAL UNION ALL
SELECT 1, 2, DATE '2021-11-21' FROM DUAL UNION ALL
SELECT 1, 3, DATE '2021-11-22' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2021-12-23' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2021-12-21' FROM DUAL UNION ALL
SELECT 2, 3, DATE '2021-12-22' FROM DUAL;
WITH cte AS (
SELECT
s.customer_id
,c.customer_name
,s.product_id
,p.product_name
,s.order_date
RANK() OVER (PARTITION BY s.customer_id ORDER BY s.order_date) rnk
FROM sales s
,customers c
,products p
INNER JOIN customers c ON s.customer_id = c.customer_id
INNER JOIN products p ON s.product_id = p.product_id
)
SELECT
customer_id
,customer_name
,product_id
,product_name
,order_date
FROM cte
WHERE rnk = 1;