Multiple Count Dinstinct in one Reflection

1

Why when i try to run a reflection to calculate two different count distinct with different cases, it doesn’t accelerate, but when i run the same reflection to calculate only one count it is accelerated correctly.

That’s my query

SELECT
TO_CHAR(CAST(Record_anagrafiche_anagrafiche_last.accesso_gialla AS TIMESTAMP),‘YYYY-MM-DD’) AS record_anagrafiche_anagrafiche_last_accesso_gialla_date_1,
COUNT(DISTINCT CASE WHEN (Record_anagrafiche_anagrafiche_last.lista_tipo_contratti LIKE ‘%FA%’) AND (Record_anagrafiche_anagrafiche_last.lista_tipo_contratti NOT LIKE ‘%DIRFA%’ AND Record_anagrafiche_anagrafiche_last.lista_tipo_contratti NOT LIKE ‘%BUS%’ OR Record_anagrafiche_anagrafiche_last.lista_tipo_contratti IS NULL) THEN Record_anagrafiche_anagrafiche_last.codice_cliente ELSE NULL END) AS record_anagrafiche_anagrafiche_last_count_app_family_1,
COUNT(DISTINCT CASE WHEN (Record_anagrafiche_anagrafiche_last.lista_tipo_contratti LIKE ‘%BUS%’) THEN Record_anagrafiche_anagrafiche_last.codice_cliente ELSE NULL END) AS record_anagrafiche_anagrafiche_last_count_app_business_1
FROM Telepass.Looker.Record_Anagrafiche.anagrafiche_last AS Record_anagrafiche_anagrafiche_last

WHERE
(Record_anagrafiche_anagrafiche_last.accesso_gialla IS NOT NULL)
GROUP BY TO_CHAR(CAST(Record_anagrafiche_anagrafiche_last.accesso_gialla AS TIMESTAMP),‘YYYY-MM-DD’)
ORDER BY 1 DESC
LIMIT 7

2

Is there a query profile you can share of both 1 and 2 countdistincts?
What type of reflection are you using on Telepass.Looker.Record_Anagrafiche.anagrafiche_last ?

3

It’s a Raw reflection

4

Have you tried an aggregation reflecrion with the approximate distinct count measure type enabled? Note that you have to enable this as it is off by default.