JQL Query Help: Counting Distinct Users by Challenge ID
I’m trying to run a JQL query to calculate the number of users who joined each challenge — specifically, I want to count the number of distinct users (distinct_id) who fired the JOIN_CHALLENGE event, grouped by the event’s challenge_id property. What I've verified so far:
The JOIN_CHALLENGE event exists and shows up when queried.
The properties.challenge_id field is present and populated on this event.
The distinct_id values are valid and non-null.
I've confirmed that mapping over these events directly (via .map()) works fine and shows both challenge_id and distinct_id.
The issue: Whenever I try to aggregate using .groupBy(['properties.challenge_id', 'distinct_id']) and then count distinct users per challenge, the final result either:
Returns 0 for users_joined, and
Omits challenge_id entirely (only showing users_joined as 0)
Here's a version of the query I’ve tried:
function main() {
return Events({
from_date: '2025-01-01',
to_date : '2025-12-31',
event_selectors: [{ event: 'JOIN_CHALLENGE' }]
})
.groupBy(['properties.challenge_id', 'distinct_id'], function(events, key) {
return null;
})
.groupBy(['key[0]'], function(state, row) {
state = state || { users_joined: 0 };
state.users_joined += 1;
return state;
})
.map(function(key, value) {
return {
challenge_id: key[0],
users_joined: value ? value.users_joined : 0
};
});
}
Despite challenge_id clearly existing in the raw event data, the result still returns:
json
[
{ "users_joined": 0 }
]
What I need help with:
How to correctly count distinct users per challenge using JQL.
Why challenge_id is being dropped or treated as undefined in grouping.
Whether there’s a known issue with groupBy(['properties.challenge_id', 'distinct_id']) → groupBy(['key[0]']) chaining.
Any clarification or recommended fix would be appreciated. Let me know if you need screenshots or JSON samples.