Order string_agg over partition | BigQuery

Here’s the piece of my query in question:

string_agg(product_type, "|" order by product_type desc) over(partition by email, processed_date) as full_basket

I’m having issues with ordering my string_agg. The problem is that one “basket” is showing “product_a | product_b” and another may be showing “product_b | product_a”, which is the same exact thing.

When I run this query, BigQuery gives me the following error: “ORDER BY in arguments is not supported on analytic functions at [14:1]”

any ideas on how to order?

Answer

BQ Documentation specifically says that OVER clauses (aka analytic functions) is incompatible with other clauses within STRING_AGG().

I think your best bet is to order before the aggregation. Use CTEs to organize and order your data, then apply STRING_AGG() at the end.