Q:

How to select the number of time segments that contain a event?

I am trying to implement a "time spent on platform" metric, grouped by user and day.
My test data has 15 events for each of two users, and those 15 events are split among three days. However, the five events for a particular user/day combo all happen at exactly the same moment, so for the purposes of my "time spent" calculation they should only be counted as a single "time unit". I'm defining a "time unit" as a minute that contains at least event for a user.
Here is my attempt so far:

SELECT SUM(x) FROM (SELECT COUNT(score_value) as x FROM user_scores GROUP BY time(1m),user_id) GROUP BY time(1d),user_id

name: user_scores
tags: user_id=123
time                sum
----                ---
1518134400000000000 5
1518220800000000000 5
1518307200000000000 5
1518393600000000000

name: user_scores
tags: user_id=456
time                sum
----                ---
1518134400000000000 5
1518220800000000000 5
1518307200000000000 5

I can see how this is the expected result set, but it is not the data I'm looking for. Since each of the five events for a single user/day combo happen at exactly the same minute, the sum values in the results should all be 1.
So, I need a way to channge SELECT COUNT(score_value) as x FROM user_scores GROUP BY time(1m),user_id into something that returns 0 or 1 depending on if there are any events occuring in that minute

A:

I figured it out, what works is as follows: 

SELECT COUNT(x) FROM (SELECT COUNT(score_value) as x FROM user_scores GROUP BY time(1m),user_id) WHERE x > 0 GROUP BY time(1d),user_id

Basically I changed the outer SELECT SUM(x) to SELECT COUNT(x) and added the where x > 0.

