CODEWITHSUNDEEP
snowflake-cloud-data-platform
Blackdynomite
Blackdynomite

Reputation: 431

SQL Count Distinct User IDs based on Column String Value

I am trying to create a new data table that has total counts of users for each week in two separate columns, a count for distinct users from Platform = 'Web' and a count of distinct users from Platform = 'iOS' or 'Android'

My attempt below did not work. Can someone advise?

DATA SOURCE:

DATE     USER_ID  Platform
1/1/2020  1223    Web
1/2/2020  2032    iOS
1/3/2020  2432    Android
1/4/2020  20311   iOS
1/4/2020  2443    Android

SQL ATTEMPT

SELECT DATE_TRUNC(week, d.DATE)  as DATE,
           COUNT(DISTINCT d.USER_ID WHERE d.PLATFORM = 'Web') AS USER_COUNT_WEB,
           COUNT(DISTINCT d.USER_ID WHERE d.PLATFORM = 'Android' OR 'iOS') AS USER_COUNT_PHONE
    FROM data d
    GROUP BY 1;

Target table:

DATE     User_Count_Web  User_count_Phone
1/1/2020  12              230
1/8/2020  20              442
1/15/2020 24              533

Upvotes: 1

Views: 72

Answers (2)

gcbenison
gcbenison

Reputation: 11963

The sum/case pattern is also useful for this kind of "pivot query" -

SELECT
  WEEK(date),
  SUM(CASE WHEN platform='Web' THEN 1 ELSE 0 END) AS count_web,
  SUM(CASE WHEN platform IN ('iOS','Android') THEN 1 ELSE 0 END) AS count_phone
FROM data
GROUP BY WEEK(date)

(The week() function is specific to mysql, and this answer gives the week as an integer offset from the start of the year rather than as an actual date, but the idea is the same.)

Upvotes: 1

eshirvana
eshirvana

Reputation: 24633

here is one way :

SELECT
    DATE_TRUNC(week, d.DATE) as DATE,
    COUNT(DISTINCT case when d.PLATFORM = 'Web' then d.USER_ID end) AS USER_COUNT_WEB,
    COUNT(DISTINCT case when d.PLATFORM in ('Android','iOS') then d.USER_ID end) AS USER_COUNT_PHONE
FROM data d
GROUP BY 1;

Upvotes: 1

Related Questions