Unique count of users on a specific app version (Mobile - iOS)

Hi Team,

I wanted to query the unique count of users (uuid) on a particular app version. I tried exploring MobileSession table.

Using the below query won’t give best/accurate results for me -
SELECT uniqueCount(uuid) from MobileSession facet appVersion since this quarter

Say for example - we released app versions 1.0.0, 2.0.0 and 3.0.0 over the last three months. If I was to use the above query - There are good chances many of our users will be grouped over the three versions.

Scenario:

Take an example I have three users:

User A - Installed app version 1.0.0, then upgraded to 2.0.0 and finally to 3.0.0
User B - started using/first installed only v3.0.0 of the app
User C - installed app version 2.0.0 never upgraded the app to 3.0.0 even if it was available

Actual results using thee above query:
I guess using the above query will yield me the below results (I assume). But that’s not what I want.

3.0.0 -> 2 users
2.0.0 -> 2 users
1.0.0 -> 1 users

My expected result is as below: Could you help me to understand how to query this ?

3.0.0 -> 2 users
2.0.0 -> 1 users
1.0.0 -> 0 users

You can find the users that upgraded by querying upgradeFrom, which will provide the app version they were using before upgrading. For example:

SELECT uniqueCount(uuid) FROM MobileSession where upgradeFrom is not null 
since 3 days ago facet appName, upgradeFrom, appVersion 

Query install to count new users:
SELECT uniqueCount(uuid) FROM MobileSession where install is true

Here are some additional examples of NRQL query examples for Mobile:

https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/nrql-query-tutorials/nrql-query-examples-mobile-monitoring