Filtering Users by Platform in SQL

Need to find all Mac users? Or Windows users? Or Android users…

Here’s a simple query.

SQL you can copy / paste

If you have an events table with a timestamp and platform you can use the queries below with little modification.

select
  platform,
  user_id
from events
where platform = 'Windows';

Perhaps you have a more complex use case. Say your users are cross-platform (like our users are). You want to find the most recent platform used. Window functions are what you need:

with _events as (
  select
    platform,
    user_id,
    row_number() over (partition by user_id order by time desc)
  from events
  where time >= now() - interval '1 month' -- to reduce runtime of query
)

select
  *
from _events
where row_number = 1
  and platform = 'Windows';

These queries are on the simple side, so that you can easily alter them to your needs (e.g. by web browser, by geography, etc).

Try it yourself?

Run this template against our sample database that mirrors real startup data. See the connection credentials, then connect in PopSQL.

Pro Tips

Importing events into your DB

Don't have an events table? You should! We export all our Mixpanel events to Postgres for advanced analysis. By doing so, you can:

• Better understand feature usage
• Qualify leads based on how they've used the product
• Monitor a feature launch
• and much more!

Make your queries flexible

In PopSQL you can pass parameters into your queries with query variables.

select
  platform,
  user_id
from events
where platform = '{{platform}}';