I want to get unique customer counts. I have reference of postgresql query. Could you please convert this query into HiveSql
SELECT COUNT(user_id) Total_profiles, COUNT(distinct user_id) FITLER (WHERE age BETWEEN 18 AND 12) as age_less_than_20 FROM customer_profiles WHERE profile_date BETWEEN '2020-01-01' AND '2020-12-31'
Advertisement
Answer
Use case expressions:
SELECT COUNT(user_id) Total_profiles, COUNT(distinct case when age BETWEEN 18 AND 12 then user_id else null end) as age_less_than_20 FROM customer_profiles WHERE profile_date BETWEEN '2020-01-01' AND '2020-12-31'
One more method for counting distinct is size(collect_set()):
SELECT COUNT(user_id) Total_profiles, size(collect_set(case when age BETWEEN 18 AND 12 then user_id else null end)) as age_less_than_20 FROM customer_profiles WHERE profile_date BETWEEN '2020-01-01' AND '2020-12-31'