SQL compilation error: [TABLE.CRM_ID] is not a valid group by expression

Question

Getting the error in the title when I run my code. I am trying to get the first product sets a customer had at their earliest subscription date. I am running this in dbt.

Here's the code bringing in the table:

, products_extended as (
    select
    crm_id,
    term_start_date,
    rate_plan_name,
    product_category
    from {{ref('products_extended')}}
)

Then attempting to group by earliest subscription for each account and array_agg the product/category names.

, first_product_set as (
    select
    crm_id,
    term_start_date,
    array_agg(distinct rate_plan_name) as first_rate_plan_names,
    array_agg(distinct product_category) as first_rate_plan_categories
    from products_extended
    where rate_plan_name not like '%Free Trial%' and rate_plan_name not like '%Free Access%'
    qualify row_number() over(partition by crm_id, term_start_date order by term_start_date asc) = 1
)

I only want to keep the first row per account based on term_start_date. Saying crm_id is not a valid group by. Any suggestions?

Code is shown above. I saw some examples nesting aggregations with problems like these, but that didn't work. Not sure where to go from here

Answer 1

The row_number you can add in the first query

, products_extended as (
    select
    crm_id,
    term_start_date,
    rate_plan_name,
    product_category,
    row_number() over(partition by crm_id, term_start_date order by term_start_date asc) rn
    from {{ref('products_extended')}}
)

and then aggregate

, first_product_set as (
    select
    crm_id,
    MIN(term_start_date),
    array_agg(distinct rate_plan_name) as first_rate_plan_names,
    array_agg(distinct product_category) as first_rate_plan_categories
    from products_extended
    where rate_plan_name not like '%Free Trial%' and rate_plan_name not like '%Free Access%'
    and rn = 1
GROUP BY crm_id
)