Beast Mode: Count IF for user_id based on another column

cusefan · 2023-08-04T20:17:52+00:00

There was an error rendering this rich post.

Relevant columns in dataset

user_id: unique identifier

badge_level: a number from 1 - 4

user_badge_id: another unique identifier. Each badge earned generates a unique id

I am trying to create a new column that counts the # of Level 3 badges the user has earned.

Then I will create a Beast Mode to calculate the following

% of users that have earned at least 1 Level 3 Badge

% of users that have earned at least 2 Level 3 Badges

% of users that have earned at least 3 Level 3 Badges

I have tried multiple different nested functions like:

COUNT(CASE WHEN `badge_level` = '3' THEN `user_id` END)
count(distinct case when 'badge_level' = '3' then concat('user_id','badge_level') end)

but keep getting the Calculation Error: All fields must be aggregated or in the group by clause

In excel/google sheets it would be something like

Have at least one = countif (badge_level = 3) / distinctcount(user_id)

Has at least 2 = countif(badge_level = 3) >= 2 / distinctcount(user_id)

Has at least 3 = countif(badge_level = 3) >= 3 / distinctcount(user_id)

Find more posts tagged with

Count

Beast Mode

This worked for me (only difference is I omitted the single quotes around 3):

Here is the data I used:

And the card with beastmode applied:

Are other filters or sorting applied to your card?

This worked for me (only difference is I omitted the single quotes around 3):

Here is the data I used:

And the card with beastmode applied:

Are other filters or sorting applied to your card?