I am trying to use date_trunc() on some date in a window function on BigQuery. I used to do this previoulsy in Snowflake and everything went smoothly. Unfortunalty, BigQuery tells me that the full date needs to be in the group by, which defeat the purpose of using the date_trunc function. I wish to group by "year-month" and customer_id and give every customer a "rank" based on their order per "year-month". Here's an example of my script
select
id as customer_id,
date_trunc(month from date) as date,
count(1) as orders,
row_number() over (partition by date_trunc(month from date) order by count(1) desc) as customer_order
from table
group by 1,2
And I get this error code :
PARTITION BY expression references column date which is neither grouped nor aggregated
Anyone knows how to prevent this problem in an elegant manner? I know I could do a subquery \ CTE to fix this but I'm curious to understand why Big Query prevent this operation.