Useful SQL Queries for Redshift

The Track call allows you to record any actions your users perform. A Track call takes three parameters: the userId, the event, and any optional properties.

Here's a basic Track call:

1
analytics.track('Completed Order',
2
  item: 'pants',
3
  color: 'blue'
4
  size: '32x32'
5
  payment: 'credit card'
6
});

A completed order Track call might look like this:

1
analytics.track('Completed Order', {
2
 item: 'shirt',
3
 color: 'green'
4
 size: 'Large'
5
 payment: 'paypal'
6
});

Each Track call is stored as a distinct row in a single Redshift table called tracks. To get a table of your completed orders, you can run the following query:

1
select *
2
from initech.tracks
3
where event = 'completed_order'

That SQL query returns a table that looks like this:

Expand image

But why are there columns in the table that weren't a part of the Track call, like event_id? This is because the Track method (for client-side libraries) includes additional properties of the event, like event_id, sent_at, and user_id!

If you want to know how many orders were completed over a span of time, you can use the date() and count function with the sent_at timestamp:

1
select date(sent_at) as date, count(event)
2
from initech.tracks
3
where event = 'completed_order'
4
group by date

That query returns a table like this:

To see the number of pants and shirts that were sold on each of those dates, you can query that using case statements:

1
select date(sent_at) as date,
2
sum(case when item = 'shirt' then 1 else 0 end) as shirts,
3
sum(case when item = 'pants' then 1 else 0 end) as pants
4
from initech.tracks
5
where event = 'completed_order'
6
group by date

That query returns a table like this:

Segment's API does not impose any restrictions on your data with regard to user sessions.

Sessions aren't fundamental facts about the user experience. They're stories Segment builds around the data to understand how customers actually use the product in their day-to-day lives. And since Segment's API is about collecting raw, factual data, there's no API for collecting sessions. Segment leaves session interpretation to SQL partners, which let you design how you measure sessions based on how customers use your product.

For more on why Segment doesn't collect session data at the API level, check out Twilio's Facts vs. Stories: Why Segment has no Sessions API blog.

Each of Segment's SQL partners allow you to define sessions based on your specific business needs. With Looker, for example, you can take advantage of their persistent derived tables and LookML modeling language to layer sessionization on top of your Segment SQL data.

To define sessions with raw SQL, a great query and explanation comes from Mode Analytics.

Here's the query to make it happen, but read Mode Analytics' blog post for more information. Mode walks you through the reasoning behind the query, what each portion accomplishes, how you can tweak it to suit your needs, and the kinds of further analysis you can add on top of it.

1
-- Finding the start of every session
2
SELECT *
3
  FROM (
4
       SELECT *
5
              LAG(sent_at,1) OVER (PARTITION BY user_id ORDER BY sent_at) AS last_event
6
        FROM "your_source".tracks
7
      ) last
8
WHERE EXTRACT('EPOCH' FROM sent_at) - EXTRACT('EPOCH' FROM last_event) >= (60 * 10)
9
   OR last_event IS NULL
10

11
-- Mapping every event to its session
12
SELECT *,
13
       SUM(is_new_session) OVER (ORDER BY user_id, sent_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS global_session_id,
14
       SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY sent_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS user_session_id
15
  FROM (
16
       SELECT *,
17
              CASE WHEN EXTRACT('EPOCH' FROM sent_at) - EXTRACT('EPOCH' FROM last_event) >= (60 * 10) 
18
                     OR last_event IS NULL 
19
                   THEN 1 ELSE 0 END AS is_new_session
20
         FROM (
21
              SELECT *,
22
                     LAG(sent_at,1) OVER (PARTITION BY user_id ORDER BY sent_at) AS last_event
23
                FROM "your_source".tracks
24
              ) last
25
       ) final

The Identify method ties user attributes to a userId.

1
analytics.identify('bob123',{
2
  email: 'bob@initech.com',
3
  plan: 'Free'
4
});

As these user traits change over time, you can continue calling the Identify method to update their changes. With this query, you can update Bob's account plan to "Premium".

1
analytics.identify('bob123', {
2
  email: 'bob@initech.com',
3
  plan: 'Premium'
4
});

Each Identify call is stored in a single Redshift table called identifies. To see how a user's plan changes over time, you can run the following query:

1
select email, plan, sent_at
2
from initech.identifies
3
where email = 'bob@initech.com'

This SQL query returns a table of Bob's account information, with each entry representing the state of his account at different time periods:

If you want to see what your users looked like at a previous point in time, you can find that data in the identifies table. To get this table for your users, replace 'initech' in the SQL query with your source slug.

If you only want the current state of the users, convert the identifies table into a distinct users table by returning the most recent Identify call for each account.

The following query returns the identifies table:

1
select *
2
from initech.identifies

That query returns a table like this:

If all you want is a table of distinct user with their current traits and without duplicates, you can do so with the following query:

1
with identifies as (
2
  select user_id,
3
         email,
4
         plan,
5
         sent_at,
6
         row_number() over (partition by user_id order by sent_at desc) as rn
7
  from initech.identifies
8
),
9
users as (
10
  select user_id, 
11
         email,
12
         plan
13
  from identifies
14
  where rn = 1
15
)
16

17
select *
18
from users

Let's say you have an identifies table that looks like this:

If you want to query the traits of these users, you first need to convert the identifies table into a users table. From there, run a query like this to get a count of users with each type of plan:

1
with identifies as (
2
  select user_id,
3
         email,
4
         plan,
5
         sent_at,
6
         row_number() over (partition by user_id order by sent_at desc) as rn
7
  from initech.identifies
8
),
9
users as (
10
  select plan
11
  from identifies
12
  where rn = 1
13
)
14

15
select sum(case when plan = 'Premium' then 1 else 0 end) as premium,
16
       sum(case when plan = 'Free' then 1 else 0 end) as free
17
from users

And there you go: a count of users with each type of plan!

The group method ties a user to a group. It also lets you record custom traits about the group, like the industry or number of employees.

Here's what a basic group call looks like:

1
analytics.group('0e8c78ea9d97a7b8185e8632', {
2
  name: 'Initech',
3
  industry: 'Technology',
4
  employees: 329,
5
  plan: 'Premium'
6
});

As these group traits change over time, you can continue calling the group method to update their changes.

1
analytics.group('0e8c78ea9d97a7b8185e8632', {
2
  name: 'Initech',
3
  industry: 'Technology',
4
  employees: 600,
5
  plan: 'Enterprise'
6
});

Each group call is stored as a distinct row in a single Redshift table called groups. To see how a group changes over time, you can run the following query:

1
select name, industry, plan, employees, sent_at
2
from initech.groups
3
where name = 'Initech'

The previous query will return a table of Initech's group information, with each entry representing the state of the account at different times.

If you want to see a group's traits at a previous point in time, this query is useful (To get this table for your groups, replace 'initech' with your source slug).

If you only want to see the most recent state of the group, you can convert the groups table into a distinct groups table by viewing the most recent groups call for each account.

The following query will return your groups table:

1
select *
2
from initech.groups

The previous query returns the following table:

However, if all you want is a table of distinct groups and current traits, you can do so with the following query:

1
with groups as (
2
  select name,
3
         industry,
4
         employees,
5
         plan,
6
         sent_at,
7
         row_number() over (partition by name order by sent_at desc) as rn
8
  from initech.groups
9
),
10
organizations as (
11
  select name,
12
         industry,
13
         employees,
14
         plan
15
  from groups
16
  where rn = 1
17
)
18

19
select *
20
from organizations

This query will return a table with your distinct groups, without duplicates.