Skip to content

Instantly share code, notes, and snippets.

@bstancil
Created March 23, 2016 22:09
Show Gist options
  • Save bstancil/90b5c4d64ff0699cc0d4 to your computer and use it in GitHub Desktop.
Save bstancil/90b5c4d64ff0699cc0d4 to your computer and use it in GitHub Desktop.
Segment users
-- DAILY
SELECT DATE_TRUNC('day', sent_at) AS day,
COUNT(DISTINCT user_id) AS users, -- These are people who are logged in
COUNT(DISTINCT anonymous_id) AS visitors -- These are all visitors, including those who might be logged out
FROM segment.tracks
GROUP BY 1
ORDER BY 1
-- WEEKLY
SELECT DATE_TRUNC('week', sent_at) AS day,
COUNT(DISTINCT user_id) AS users,
COUNT(DISTINCT anonymous_id) AS visitors
FROM segment.tracks
GROUP BY 1
ORDER BY 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment