Lessons learnt from the 8 week sql challenge: string_agg

👩🏼‍💻 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.