BigQuery aggregations on different level by grouping sets

Question

I need to create an aggregation for each housing type (column type) for each destination. There is a hierarchy (product->sub_product) and (category->sub_category) and also product can have different categories and each category can be linked to every product. But first a product need to be selected so the hierarchy is product(sub_product)->industry_category(industry_sub_category) I tried with the query below and all-all-all-all combinations for given destination and type is correct, but the other combinations are off. Any ideas what need to be changed or how to optimize?

WITH test_table AS (
SELECT
 CASE WHEN date between date_column between ’2025-01-01’ AND ‘2025-01-31 THEN ‘Jan’
    WHEN date between date_column between ’2025-02-01’ AND ‘2025-02-28 THEN ‘Feb’
ELSE ‘NA’ END AS target_month,
 IF(GROUPING(product_group) = 1, 'all', product_group) AS title_group,
 IF(GROUPING(product) = 1, 'all', product) AS title,
 IF(GROUPING(category) = 1, 'all', category) AS industry_category,
 IF(GROUPING(sub_category) = 1, 'all', sub_category) AS industry_sub_category,

 ‘destination’ AS dimension_type,
 destination AS dimension_value,
 ‘housing_type’ as related_type,
 type as related_value,


 sum(type_count) AS counter

FROM
 table_a

GROUP BY
GROUPING SETS(
 (target_month,product_group,
  product,
  category,
  sub_category,destination,type),
  (target_month,product_group,
  product,category,destination,type),
   (target_month,product_group,
  product,destination,type),
  (target_month,product_group,destination,type),
  (target_month,destination,type))

)
SELECT * FROM test_table;

Here are the logic I'm aiming for:[](https://i.sstatic.net/ZhDklNmS.jpg)

Answer 1

I noticed your syntax in your CASE statement. The CASE statement has repeated BETWEEN keywords: date between date_column between .... I think this is incorrect SQL syntax. Try to fix the syntax. It should likely be date_column BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'.

Also, try adding a WHERE clause to filter the dates before the GROUP BY. This ensures only relevant data enters the aggregation pipeline.