Extracted Date within a Partition By Function

[imercann]

Hi,

I am trying to run the following query but BigQuery shows an error: PARTITION BY expression references column date which is neither grouped nor aggregated

SELECT
EXTRACT(MONTH FROM date) month,
country_name,
SUM(installs),
DENSE_RANK() OVER(PARTITION BY EXTRACT(MONTH FROM date) ORDER BY SUM(installs) DESC) as ranking
FROM `tableA`
WHERE EXTRACT(year FROM date) = 2022
GROUP BY month, country_name
ORDER BY month;

 When I group by the "date" field, the result is duplicated for each date. How can I solve the problem?

Any help is appreciated

[VishalBulbule]

@imercann 

You can modify  query as below and try

SELECT
month,
country_name,
SUM(installs),
DENSE_RANK() OVER(PARTITION BY month ORDER BY SUM(installs) DESC) as ranking
FROM (
SELECT
EXTRACT(MONTH FROM date) AS month,
country_name,
installs
FROM `tableA`
WHERE EXTRACT(YEAR FROM date) = 2022
)
GROUP BY month, country_name
ORDER BY month;