Compute Count for each interval

SQL++
,
1

I have query to compute space usage per account

select RAW sum(CASE WHEN ci.durationMinutes IS NOT VALUED THEN 0 ELSE ci.durationMinutes END)
	from `bucket` as act 
	LEFT OUTER JOIN `bucket` as ci 
		on META(act).id = ci.token  and ci.type="catalog" AND ci.status="RECORDED"
	where 
		act.ts = 32503680000 
	group by
		META(act).id

I need to figure out how to calculate number of accounts that are in specific interval based on space usage. I think this is possible using using CASE … WHEN construction (see below), but I couldn’t construct the exact query. Any ideas?

select duration, count(duration) from( 				
 select RAW sum(CASE WHEN ci.durationMinutes IS NOT VALUED THEN 0 ELSE ci.durationMinutes END)
	from `bucket` as act 
	LEFT OUTER JOIN `bucket` as ci 
		on META(act).id = ci.token  and ci.type="catalog" AND ci.status="RECORDED"
	where 
		act.ts = 32503680000 
	group by
		META(act).id) space
let duration = CASE WHEN space =< 15 THEN "dur15"
					WHEN `dur` > 15 AND `dur` =< 30 THEN "dur30"
					WHEN `dur` > 30 AND `dur` =< 45 THEN "dur45"
				ELSE THEN "durOthers" END
group by duration;
2

your query is right if u replace dur with space. Also SUM(), AVG()s only considers numeric values and other types are ignored.

SELECT duration, COUNT(1) AS cnt
FROM (SELECT RAW SUM(ci.durationMinutes)
      FROM `bucket` AS act
      LEFT OUTER JOIN `bucket` AS ci ON META(act).id = ci.token AND ci.type="catalog" AND ci.status="RECORDED"
      WHERE act.ts = 32503680000
      GROUP BY META(act).id) AS d
LET duration = CASE WHEN d <= 15 THEN "dur15"
                    WHEN d > 15 AND d <= 30 THEN "dur30"
                    WHEN d > 30 AND d <= 45 THEN "dur45"
                   ELSE "durOthers" END
GROUP BY duration;