How to use dense_rank, aliasing and groupby in a query?

Viewed 31

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

0 Answers
Related