👩🏼💻 using Google BigQuery SQL syntax
📁 check the Github repository for context
A neat function I discovered during the challenge is STRING_AGG
which strings together (concatenates) non null string values in a single field.
This was by no means a requirement but instead of the output below, I wanted to return a table that shows the 3 customer id's and for each customer id strings together the different product names in a single cell.
So what's the syntax for creating the output below?
Turns out it's a lot simpler than I thought.
Instead of this ...
SELECT
customer_id,
product_name
FROM q5_items_sold_ranked
WHERE count_rank = 1
ORDER BY customer_id ASC;
... do this.
SELECT
customer_id,
STRING_AGG(product_name, ', ') AS most_popular_items
FROM q5_items_sold_ranked
WHERE count_rank = 1
GROUP BY customer_id
ORDER BY customer_id ASC;
Hope you find this useful, let me know in the comments if any questions.
Top comments (0)