cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Number of distinct users

mkulov
Organizer

Hi,

 

Any idea how to calculate the numer of unique tagged users over a period of time?

I tried the query below however it returns multiple rows with value 1.

This is great so far. 

Now I only need to sum up all rows.

Any Idea how to caluclate the sum of all distinct users?

 

SELECT count(DISTINCT userId) FROM usersession GROUP BY userId

 

Thank you,

Martin Kulov

 

Digital Performance Optimizer
4 REPLIES 4

DanielS
DynaMight Pro
DynaMight Pro

Hi,

 

I suggest this one:

 

SELECT count(DISTINCT ip) FROM usersession WHERE userType IS "REAL_USER" AND newUser =true

 

also you can filter by application name.

 

Hope it helps

The true delight is in the finding out rather than in the knowing.

ABB
Frequent Guest

I had earlier used DISTINCT userId instead of DISTINCT ip and both have different counts. Which one is preferred?

Another confusing part is for application filtering. For the above USQL, I see selection of useraction.application, whereas the distinct user count is from usersession table. Will it be okay to use useraction.application to filter by application?

 

SELECT count(DISTINCT userId) FROM usersession WHERE userType IS "REAL_USER" AND newUser =true   ....returns more distinct records

 

SELECT count(DISTINCT ip) FROM usersession WHERE userType IS "REAL_USER" AND newUser =true .....returns lesser distinct records

 

DanielS
DynaMight Pro
DynaMight Pro

I couldn't establish a general rule, but personally, I preferred IP. Why? Because if the same user is in the office or at home, the public IP is the same also if he uses different devices because the public IP is NATed for those local devices. It may happen that if the user disconnects the Wifi device and switches to 4G/5G, the IP changes, but it could allow this Beta(error) because it is not the most common behavior. I have done tests this way and for me it is much more convenient.
Another scenario that has been evaluated is that of different users from the same home/office looking for the same page. But I have done tests and with the exception of newspapers/big shopping sites/social networks. It is highly unlikely that the pages will tend to be the same. So if your app is from the sites described above, maybe another approach is needed, but for everyone else it's good.

The true delight is in the finding out rather than in the knowing.

mkulov
Organizer

it was the GROUP BY clause that was bugging me 🙂

 

Digital Performance Optimizer