Whenever I change a filter value for my SQL question its visualization reverts back to table view. From any kind of chart.
Moreover my preferred visual for this question's results is pie chart and it is not among highlighted choices, despite displaying results in expected manner if picked.
My query:
WITH t2 AS (
WITH t1 AS (
SELECT m.name AS merchant, pc.name AS category, count(*) AS total
FROM dictionary_data."transaction" t
LEFT JOIN dictionary_data.product_catalog_product pcp
ON pcp.product_id = t.product_id
LEFT JOIN dictionary_data.product_category pc
ON pc.id = pcp.product_category_id
LEFT JOIN dictionary_data.merchant m
ON m.id = pc.merchant_id
WHERE pc.enabled = TRUE
AND t.batch_receive_date >= to_date('01.05.22', 'DD.MM.YY')
[[AND (t.batch_receive_date >= {{data_from}}) AND (t.batch_receive_date < {{data_to}}+1)]]
[[AND UPPER(t2.merchant) LIKE UPPER('%'||{{Merchant}}||'%')]]
GROUP BY 1, 2
)
SELECT t1.merchant, t1.category, t1.total,
ROW_NUMBER() OVER (PARTITION BY t1.merchant ORDER BY t1.total DESC) AS category_rank
FROM t1
)
SELECT t2.merchant, t2.category, t2.total
FROM t2
WHERE t2.category_rank <= 5
ORDER BY 1, 3 DESC
;