-
Your SQL query has an issue with referencing nested and possibly repetitive fields in a SAFE_OFFSET function when the type condition is met. Since the v2ProductName field is only non-repetitive in ‘EVENT’ instances, it is plausible the function might return null, non-existent, or throw an error for other event types.
One option could be to flatten or UNNEST the ‘product’ field to a selectable structure before this case statement. This way, you can select the v2ProductName or its conditionally offset variant in all circumstances.
Regarding the nature of your desired output, you’d presumably want to encapsulate the logic in an encapsulating select statement or the FROM clause that filters only non-purchasing visitors. You could ensure this by using an outer join or where-not-exists clause for users with corresponding purchase records.
Also, understand that SQL is set-based logic and does not work well with ‘lists,’ per se. The whole concept of OFFSET means nothing in strict relational database design, and therefore, using functions like SAFE_OFFSET might lead to unpredictable behavior that does not adhere well with the raw logic of SQL. Consequently, having repetitive fields and the requirement to select a specific instance within these is generally a symptom of non-optimal table structure for a relational database context.
In conclusion: restructure your data so that it is “clean” in a SQL context. Preferably each entity and attribute should exist only once, and foreign keys should be used to deal with one-to-many or many-to-many relationships.
