How do I use array_agg with a condition?

Question

I have a table with a list of potential customers, their activity, and their sales representative. Every customer can have up to 1 sales rep. I've built a summary table where I aggregate the customer activity, and group it by the sales rep, and filter by the customer creation date. This is NOT a cohort (the customers do not all correspond to the scheduled_flights, but rather this is a snapshot of activity for a given period of time) It looks something like this:

Now, in addition to the total number of customers, I'd also like to output an array of those actual customers. The customers field is currently calculated by performing sum(is_customer) as customers and then grouping by the sales rep. To build the array, I've tried to do array_agg(customer_name) which outputs the list of all customer names -- I just need the list of names who also satisfy the condition that is_customer = 1, but I can't use that as a where clause since it would filter out other activity, like scheduled and completed flights for customers that were not new.

Answer 1

This should probably work:

array_agg(case when is_customer = 1 then customer_name end) within group (order by customer_name)

Snowflake should ignore NULL values in the aggregation.