CODEWITHSUNDEEP
sqlpostgresqlhivehiveql
balams
balams

Reputation: 85

Find Unique Count Postgresql query into hivesql

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'

Upvotes: 0

Views: 36

Answers (1)

leftjoin
leftjoin

Reputation: 38290

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'

Upvotes: 2

Related Questions