[SOLVED] Extracted Date within a Partition By Function

Extracted Date within a Partition By Function

I am trying to run the following SQL 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;

I tried to use Trunc, format_date functions but didn't work.

Solution

Simple workaround in this case without using a subquery is

DENSE_RANK() OVER(PARTITION BY EXTRACT(MONTH FROM ANY_VALUE(date)) ORDER BY ...

since for any value in a monthly group, EXTRACT(MONTH FROM ANY_VALUE(date)) will have same value of month.

WITH `tableA` AS (
  SELECT DATE '2022-01-01' date, 'KOR' country_name, 10 installs UNION ALL
  SELECT DATE '2022-01-02' date, 'USA' country_name, 20 installs
)
SELECT EXTRACT(MONTH FROM date) month,
       country_name,
       SUM(installs),
       DENSE_RANK() OVER(PARTITION BY EXTRACT(MONTH FROM ANY_VALUE(date)) ORDER BY SUM(installs) DESC) as ranking
  FROM `tableA`
 WHERE EXTRACT(year FROM date) = 2022
 GROUP BY month, country_name
 ORDER BY month;

--Query results

+-------+--------------+-----+---------+
| month | country_name | f0_ | ranking |
+-------+--------------+-----+---------+
|     1 | USA          |  20 |       1 |
|     1 | KOR          |  10 |       2 |
+-------+--------------+-----+---------+