Segment users

gistfile1.sql

-- 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