Extracted Date within a Partition By Function

Question

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.

Hi @NickW It is a SQL query. I am fresh to data field but if I'm not wrong, that may be the answer for your question — Ihsan Mercan, Mar 25 '23 at 14:54
Hi - I know it is a SQL query, I need to see the definition of the table the SQL is querying I.e. tableA — NickW, Mar 25 '23 at 15:13

score 0 · Accepted Answer · edited Mar 25 '23 at 15:21

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 |
+-------+--------------+-----+---------+

Extracted Date within a Partition By Function

1 Answers1