u/Intrepid_Shake_1438 — reddlx

I have the following code that is technically bucketing my data correctly, but it's not doing what I intended.

The query is counting the UserId__c every time it falls into a bucket, but I want it to only capture the FIRST bucket it falls into.

SELECT COUNT( DISTINCT UserId__c),
CASE
WHEN DATEDIFF('day', LoginTime__c, NOW()) BETWEEN 0 AND 7 THEN '0 - 7 Days'
WHEN DATEDIFF('day', LoginTime__c, NOW()) BETWEEN 8 AND 14 THEN '08 - 14 Days'
WHEN DATEDIFF('day', LoginTime__c, NOW()) BETWEEN 15 AND 30 THEN '15 - 30 Days'
WHEN DATEDIFF('day', LoginTime__c, NOW()) > 30 THEN '31+ Days'
END AS Bucket
FROM LoginHistory__dlm l
INNER JOIN User_Temp__dlm u
ON l.UserId__c = u.user_ID__c
GROUP BY Bucket
ORDER BY Bucket asc

I'm getting the following results:

Bucket	Count of Rows
0 - 7 Days	1,229
08 - 14 Days	1,337
15 - 30 Days	1,246
31+ Days	1,889

When I remove the buckets, the true count of DISTINCT UserId__c is 1,912 - this total is correct.

How do I stop the query from counting every instance of UserId__c?

This is in Salesforce CRMA, so it's technically Data 360 SQL (if that matters).