I have something like this that works in bigquery:
SELECT
username, territory,
CASE
WHEN category LIKE '%bh%' THEN 'BH1'
WHEN category LIKE '%hop%' THEN 'BH2'
ELSE 'BH3'
END AS category2,
MIN(date) AS first_date,
MAX(date) AS last_date,
SUM(price) AS total_price
FROM
TABLE1
WHERE
date > '2022-01-01'
GROUP BY
username, territory,
CASE
WHEN category LIKE '%bh%' THEN 'BH1'
WHEN category LIKE '%hop%' THEN 'BH2'
ELSE 'BH3'
END
However I need to include:
DENSE_RANK() OVER (PARTITION BY username ORDER BY other_date) - DENSE_RANK() OVER (PARTITION BY username, category2 ORDER BY other_date) AS ranking
and group by username, territory, category2 and ranking.
What is a functional way to include the dense_rank which uses the category2 alias and also include it in the groupby? I tried a few things but I either end up with 'cannot use analytics function in groupby' or some other issue with using category2 in it. Thanks