How can I use the index of a "special" value from a nested column (ex: the index of the max value in that nested column) to select a value from another nested column using that index?

As an example, consider a table with the following schema:

The first few rows look like:

I want to select the most ordered product for each id's most recent order and exclude orders that don't have a most ordered product (for instance if a customer ordered the same number of Apple, Orange and Pear).

The query I currently use is a chain of CTEs, one for each product type plus an extra column which is the max number of products ordered by each user (max_ordered). I then join together the CTEs using the id column:

WITH RANKED_ORDERS AS( 
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_time DESC) AS rn
FROM mycompany.engagement.products_ordered),

LATEST_ORDERS AS(
SELECT * FROM RANKED_ORDERS WHERE rn = 1),

-- ---------------------- Apples Ordered -----------------------
APPLES_INDEXED AS(
SELECT id, username, ordered, flattened_products, offset_nk, update_time, rn
FROM LATEST_ORDERS
CROSS JOIN UNNEST(LATEST_ORDERS.products.list) AS flattened_products
WITH OFFSET as offset_nk
WHERE flattened_products.item in ('Apple')
ORDER BY offset_nk),

APPLES_ORDERED AS(
SELECT id, username, update_time, ordered.list[OFFSET(offset_nk)].item as apples_ordered 
FROM APPLES_INDEXED 
ORDER BY
update_time ASC),

-- ---------------------- Oranges Ordered ----------------------
ORANGES_INDEXED AS(
SELECT id, username, ordered, flattened_products, offset_nk, update_time, rn
FROM LATEST_ORDERS
CROSS JOIN UNNEST(LATEST_ORDERS.products.list) AS flattened_products
WITH OFFSET as offset_nk
WHERE flattened_products.item in ('Orange')
ORDER BY offset_nk),

ORANGES_ORDERED AS(
SELECT id, username, update_time, ordered.list[OFFSET(offset_nk)].item as oranges_ordered 
FROM ORANGES_INDEXED 
ORDER BY
update_time ASC),

-- ---------------------- Pears Ordered -----------------------
PEARS_INDEXED AS(
SELECT id, username, ordered, flattened_products, offset_nk, update_time, rn
FROM LATEST_ORDERS
CROSS JOIN UNNEST(LATEST_ORDERS.products.list) AS flattened_products
WITH OFFSET as offset_nk
WHERE flattened_products.item in ('Pear')
ORDER BY offset_nk),

PEARS_ORDERED AS(
SELECT id, username, update_time, ordered.list[OFFSET(offset_nk)].item as pears_ordered 
FROM PEARS_INDEXED 
ORDER BY
update_time ASC),

-- --------------- Max Product Ordered per Order --------------
MAX_ORDERED AS(
SELECT
id, username, MAX(orders_per_username.item) as max_ordered, total_orders
FROM
LATEST_ORDERS, UNNEST(ordered.list) as orders_per_username
GROUP BY id, username, total_orders),

-- -------------------- Orders In Columns ---------------------
ORDERS_IN_COLUMNS AS(
SELECT APPLES_ORDERED.username, APPLES_ORDERED.update_time, APPLES_ORDERED.apples_ordered,
ORANGES_ORDERED.oranges_ordered, PEARS_ORDERED.pears_ordered, MAX_ORDERED.max_ordered
FROM APPLES_ORDERED
LEFT JOIN ORANGES_ORDERED ON ORANGES_ORDERED.id = APPLES_ORDERED.id
LEFT JOIN PEARS_ORDERED ON PEARS_ORDERED.id = APPLES_ORDERED.id
LEFT JOIN MAX_ORDERED ON MAX_ORDERED.id = APPLES_ORDERED.id),

-- ------- Orders with a most ordered product -----------------
NO_CONFLICTS AS(
SELECT * FROM ORDERS_IN_COLUMNS
WHERE
max_ordered > 0 AND
(
    (apples_ordered not in (oranges_ordered, pears_ordered) AND apples_ordered = max_ordered)
OR
    (oranges_ordered not in (apples_ordered, pears_ordered) AND oranges_ordered = max_ordered)
OR
    (pears_ordered not in (apples_ordered, oranges_ordered) AND pears_ordered = max_ordered)
)
)

SELECT * FROM NO_CONFLICTS

This returns the following table:

which is expected.
However, I can't figure out how to simply return a table that looks like:

I'm also fairly certain that although this query basically works (I end up doing post-processing in Python to get to the last step) it might be extremely inefficient given the extensive use of "common table expressions".
Is there a more efficient way to query my BigQuery table than what I've written or would I need to completely restructure the table to get any speedup? It currently takes ~10s to run this query on a table with ~10,000 rows and 12 columns and I believe the slowness is due to the multiple CTEs.
I've been banging my head against the wall for the past two weeks trying to improve my query and haven't made much headway. Any help is sincerely appreciated!

CodePudding user response：

Consider below approach

with latest_orders as (
  select * from `mycompany.engagement.products_ordered`
  where true 
  qualify 1 = row_number() over(partition by id order by update_time desc)
), qualified_items as (
  select *, 
    array(
      select offset from t.ordered.list with offset 
      where true 
      qualify 1 = rank() over(order by item desc) 
    ) items
  from latest_orders t
)
select id, username, update_time,
  products.list[offset(items[offset(0)])] as max_product_ordered,
from qualified_items
where array_length(items) = 1    

if applied to sample data in your question - output is