使用DISTINCT ON更简单：

SELECT avg(o.quantity) AS avg_quantity
     , sum(o.revenue)  AS total_revenue
FROM  (
   SELECT DISTINCT ON (user_id)
          user_id, product
   FROM   orders
   ORDER  BY user_id, order_date
   ) init
JOIN   orders o USING (user_id, product)
WHERE  init.product = 'books';

fiddle
哪种查询样式最有效主要取决于基数。总共有多少行，有多少用户，有多少书籍订单，有多少书籍的初始订单，平均行大小，平均大小为product。
如果还有一个表users，每个相关的user_id保存一行，则可以更简单，更快。
你需要一个索引来支持查询。最好是(user_id, order_date, product)。同样，最好的指数取决于上述未公开的细节。
而且(user_id, order_date)必须是唯一的，否则就有歧义。
请参阅：

• 是否选择每个GROUP BY组中的第一行？
• 优化GROUP BY查询以检索每个用户的最新行

这个怎么样？

with order_cte as 
(
  SELECT
  *,
  ROW_NUMBER() OVER(partition by user_id order by order_date) as row_num
  from orders
 )
 
 SELECT
 AVG(QUANTITY)AVG_QUANTITY
 , SUM(REVENUE)TOTAL_REV
 FROM ORDERS
 WHERE PRODUCT = 'books'
   and USER_ID IN (
                   SELECT
                    USER_ID
                     FROM ORDER_CTE
                    WHERE PRODUCT = 'books'
                      AND ROW_NUM = 1
                 )

这个版本的查询更容易阅读。

WITH first_orders AS (
    SELECT
        DISTINCT
        user_id, 
        FIRST_VALUE(product) OVER (PARTITION BY user_id ORDER BY order_date) as first_product
    FROM orders
    GROUP BY user_id, order_date, product
)
SELECT 
    AVG(o.quantity) AS avg_quantity, 
    SUM(o.revenue) AS total_revenue
FROM orders o
JOIN first_orders fo
ON o.user_id = fo.user_id
WHERE fo.first_product = 'books' AND o.product = 'books';

你的问题的逻辑是有缺陷的（下面的所有代码都可以在fiddle here上找到）！

> A) the average quantity of books that this cohort purchased (in this
> case, 2.25, which is the average of 1, 2, 2, and 4), and;
> 
> B) the total revenue of each of these purchases (in this case, 152).

您想要计算两个用户（1和4）的两本书的平均购买量，但这四次购买的总收入不是152，而是323！
这是由计算（见表人口在小提琴）

• 用户_1 =（1 x 15）+（2 x 25）= 15 + 50 = 65
• 用户_2 =（2 x 95）+（4 x 17）= 190 + 68 = 258
• 65 + 258 = 323
  323是两个用户的总收入的正确总和。

152 = 15 + 25 + 95 + 17，即计算中没有考虑数量！
该表是根据您的数据构建和填充的。
然后，我运行了以下SQL：

WITH cte AS
(
  SELECT
    user_id,   -- some of these fields are not necessary
    product,   -- they just help in getting the full picture.
    order_date,
    SUM(revenue * quantity) OVER (PARTITION BY user_id, product)     AS rev,
    AVG(quantity)  OVER (PARTITION BY user_id, product) AS avg_qty,
    ROW_NUMBER()            OVER (PARTITION BY user_id ORDER BY user_id, order_date) AS rn
  FROM 
    orders
  ORDER BY user_id, order_date
)
SELECT 
  SUM(rev)  AS tot_rev,
  ROUND(AVG(avg_qty), 2) AS avg_q
FROM 
  cte
WHERE 
  product = 'books' AND rn = 1;

测试结果：

tot_rev     avg_q
323     2.25

这就是正确答案！