我有一个查询，它找到了购买的最大金额的项目。它似乎工作正常（见下文）。
我有两个问题，第一，有没有更好的方法重写这个查询，而不使用2 CTE，也不在输出中显示rnk的值。
其次，在另一个查询中，我如何显示此项目的product_id、product_name、price、customer_id、first_name、last_name、total_qty、total_amt。根据提供的数据，我希望看到3行。

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;

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;

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
);

INSERT  INTO purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) 
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

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);

/* 
get item with GREATEST amount of purchases 
*/
 with cte as
      (SELECT
            i.product_id,
            i.product_name,
            i.price,
            SUM (p.quantity) AS total_qty,
      SUM (p.quantity * i.price)         AS total_amt
      FROM    purchases  p
      JOIN    customers  c  ON  p.customer_id = c.customer_id
      JOIN    items   i  ON  p.product_id  = i.product_id
     GROUP BY
        i.product_id,
        i.product_name,
        i.price, p.quantity),
   cte2 as
     (SELECT product_id,
         product_name,
         price,
                      total_qty,
         total_amt,
         RANK() OVER (ORDER BY total_amt DESC) rnk 
    FROM   cte)
   SELECT *
   FROM   cte2
   WHERE  rnk = 1;

PRODUCT_ID  PRODUCT_NAME    PRICE   TOTAL_QTY   TOTAL_AMT   RNK
102 White Shirt 10.99   80  879.2   1