Calculating Active Users in SQL

Calculating Active Users in SQL
November 13, 2015 | Andy Granowitz

#sql

How engaged are your users? How frequently do they visit your website or app? Analytics services like Google Analytics and MixPanel calculate basic counts of daily, weekly, and monthly active users, but it’s difficult to customize or join these results with other data. Writing this query in SQL gives you more control. Let’s do it!

Here’s a table of user logins by day. How many users were active in the last week and month?

date	user_id	num_logins
10/1/15	1	3
10/1/15	2	null
10/1/15	3	1
10/2/15	1	null
10/2/15	2	1
10/2/15	3	3

Like calculating running totals, there are two approaches: self join or window function.

In either approach, it’s helpful to have a table of logins per user for each day, even if the user didn’t login (null in this example). If your data isn’t already organized like this, you can generate a table with a row per day, per user, with the following query (this is Postgres syntax, for other databases, modify the generate_series function to generate a table of dates).

select d.date, u.user_id, c.num_logins from ( select * from -- fill in the minimum date in your dataset generate_series('01-05-15'::timestamp, current_date::timestamp, '24 hours') as date ) d full outer join (select distinct(user_id) as user_id from userActivityTable) u on 1 = 1 full outer join userActivityTable c on u.user_id = c.user_id and c.date = d.date;

To use this data, you can create a temporary table, use a common table expression, or include it as a subselect.

Approach 1: Self Join

A self join is when you join a table with itself. How meta is that? For each row, we ask how many logins that user had in the last week. The join condition requires emails to match and for the date to be within the last 7 days. In line 5, the query sums num_logins for those dates. The case statement identifies the user as active on that day if she had any logins in the prior week.

select o.user_id, o.date, case when sum(i.num_logins) >= 1 then TRUE else FALSE end as active from userActivityTable as o join userActivityTable as i on i.date <= o.date AND i.date >= (o.date :: date) - integer '7' AND i.user_id = o.user_id group by o.user_id, o.date;

This query generates a table that tells us which users are seven-day-active over time. This result can be aggregated further, filtered for specific dates, used to find inactive users, and joined with other data. In Wagon, we can create a graph of the number of 7 day active users over time.

Approach 2: Window Functions

The self join works great, but modern databases have a more efficient way to get the same results. With window functions, we can explicitly aggregate only over rows that we care about with just a single pass through the data. If you have millions or billions of rows (lucky you), the self join will take a long time to compute. In line 5, the query sums num_logins for the user’s previous 14 days. It first partitions the table by email, then evaluates over a set of rows - in this case we’re looking at a specific date range. The case statement classifies the user as active or not just as before.

select user_id, date, case when sum(num_logins) over (partition by user_id order by date rows between 14 preceding and current row) >= 1 then TRUE else FALSE end as active from userActivityTable;

This query makes it easier to add additional metrics for 7 and 30 day active users. As expected, the wider your definition of active user, the more you’ll have. Use these new powers carefully!

Want to learn more SQL? Join us on Monday, November 16 at the Wagon office in San Francisco for a free SQL workshop. Please RSVP!