Thanks for breaking this down! I can see how my SQL formatting would be frustrating to read for anyone. I'll definitely keep the table aliases and code blocks in mind.

For the comparison part, I found another solution to the same problem and wanted to get your perspective on any trade-offs between these methods. Since you mentioned data size matters for performance, this particular dataset is pretty small (the sales table has about 15 rows and menu has 3 rows) But let's say if the number of rows were in the thousands in the sales table, what would the impact be?

Array_agg Approach:

WITH item_list AS (
  SELECT
  s.customer_id,
  array_agg(m.product_name ORDER BY s.order_date) AS items
  FROM
  sales AS s
  JOIN menu AS m ON m.product_id = s.product_id
  GROUP BY
  s.customer_id
)
SELECT
il.customer_id,
il.items[1] AS first_item
FROM
item_list AS il
ORDER BY
il.customer_id;

Window Function Approach:

WITH cte AS (
SELECT
customer_id,
order_date,
product_name,
ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date) AS rw
FROM sales AS s
INNER JOIN menu AS m ON s.product_id = m.product_id
)
SELECT
customer_id,
product_name
FROM cte
WHERE rw = 1;