This builds on work from my Walk Through of Using Postgresql and Blazer to Generate a Cohort Report.
The query I’ll be building helps answer what percentage of active users commented on at least one welcome article. For this query, an active user is someone who’s been on the site 4 of the last 7 days.
Establishing the Entity Relationship Model
Again, when writing Structured Query Language (SQL 📖), I like to start with a relationship diagram. In this data foray, we have four relevant tables.
class User
has_many :articles
has_many :comments
end
class Article
belongs_to :user
has_many :comments, as: :commentable
end
class Comment
belongs_to :commentable, polymorphic: true
belongs_to :user
end
class PageView
belongs_to :article
belongs_to :user, optional: true
end
Below is a diagram for those of you who prefer an ERM of the four data models.
Querying All Active Members
I want all users who have page_views on at least 4 of the last seven days; we’ll consider these “active users.”
First I want to build a very narrow query; one that lets me make sure I know I’m on the right path. I’ll limit the page views to my user_id:
SELECT user_id, extract(isodow from created_at) AS day_of_week
FROM page_views
WHERE page_views.user_id = 702612
AND page_views.created_at > CURRENT_DATE - 7
GROUP BY page_views.user_id, day_of_week
The following query times out; It’s trying to query all users.
SELECT dow.user_id, count(dow.day_of_week) AS number_of_days FROM (
SELECT user_id, extract(isodow from created_at) AS day_of_week
FROM page_views
WHERE page_views.created_at > CURRENT_DATE - 7
AND user_id IS NOT NULL
GROUP BY page_views.user_id, day_of_week) AS dow
GROUP BY dow.user_id
HAVING count(dow.day_of_week) >= 4
update
On DEV.to, someone pointed me to a simplification:
SELECT
user_id,
count( DISTINCT extract(isodow from created_at)) AS created_at_ctd
FROM
page_views
WHERE
created_at > CURRENT_DATE - 7
AND
user_id IS NOT NULL
GROUP BY
user_id
HAVING
count( DISTINCT extract(isodow from created_at)) >= 4
;
Because of the enormity of the page views we need to limit to only recently updated users. The following is the query to get recent users.
SELECT id
FROM users
WHERE updated_at > CURRENT_DATE - 7
The following query is the foundational “Who are the current active users of Forem.”
SELECT DISTINCT dow.user_id FROM (
SELECT users.id AS user_id,
extract(isodow from page_views.created_at) AS day_of_week
FROM users
INNER JOIN page_views
ON page_views.user_id = users.id
AND page_views.created_at > CURRENT_DATE - 7
AND user_id IS NOT NULL
-- Extend the window for users just a bit to account for timing variance --
WHERE users.updated_at > CURRENT_DATE - 8
GROUP BY users.id, day_of_week) AS dow
GROUP BY dow.user_id
HAVING count(dow.day_of_week) >= 4
I “saved” the above query to https://dev.to/admin/blazer/queries/717-regular-and-active-recent-users-of-dev. We now have our “who’s the currently active users of DEV.to” query.
Querying Active Users Who Have Commented on a Welcome Post
The next part is to work out who all commented on a welcome post. In Walk Through of Using Postgresql and Blazer to Generate a Cohort Report, I wrote about finding the users who had commented on the welcome article.
However, I need to adjust the cohort query; I only want users who commented on the welcome post. The cohort query has users who did and did not comment on the welcome post.
As a quick reminder, the result of the following query is all user_id that commented on a welcome post; but with a limitation on the user’s updated_at
SELECT DISTINCT comments.user_id AS user_id
FROM comments
INNER JOIN users
ON comments.user_id = users.id
-- Extend the window for users just a bit to account for timing variance --
AND users.updated_at > CURRENT_DATE - 8 day
INNER JOIN articles
ON comments.commentable_id = articles.id
AND comments.commentable_type = 'Article'
AND articles.title LIKE 'Welcome Thread - v%'
AND articles.published = true
AND articles.user_id = 3
GROUP BY comments.user_id
Now to meld the two queries. I’m using the Postgresql WITH statement to create two queries that I can reference later on. I find the WITH statement to help “encapsulate” queries and hopefully make them more conceptually understandable.
WITH cow AS (
-- User IDs of recent folks who have commented on the
-- welcome threads --
SELECT DISTINCT comments.user_id AS user_id
FROM comments
INNER JOIN users ON comments.user_id = users.id
-- Extend the window for users just a bit to account --
-- for timing variance --
AND users.updated_at > CURRENT_DATE - '8 day'
INNER JOIN articles ON comments.commentable_id = articles.id
AND comments.commentable_type = 'Article'
AND articles.title LIKE 'Welcome Thread - v%'
AND articles.published = true
AND articles.user_id = 3
GROUP BY comments.user_id
), dow AS (
-- User IDs of folks who have interacted at least 4 different
-- days of this week --
SELECT user_id FROM (
SELECT users.id AS user_id,
extract(isodow from page_views.created_at) AS day_of_week
FROM users
INNER JOIN page_views
ON page_views.user_id = users.id
AND page_views.created_at > CURRENT_DATE - 7
AND user_id IS NOT NULL
-- Extend the window for users just a bit to account for
-- timing variance --
WHERE users.updated_at::date > CURRENT_DATE - 8
GROUP BY users.id, day_of_week
) AS dows
GROUP BY user_id
HAVING COUNT(day_of_week) >= 4
)
SELECT COUNT(dow.user_id) AS count_of_users,
(
SELECT COUNT(*)
FROM dow
INNER JOIN cow
ON cow.user_id = dow.user_id
) AS count_of_users_that_said_hello
FROM dow
Conclusion
When I was writing the last query, I kept getting a result that said every active user on the site had commented on a welcome article. I didn’t trust that result; it seemed highly improbable. I revisited my queries and logic, found my error, reworked the query and got a more reasonable answer.
What was wrong? I had copied and pasted a query from Walk Through of Using Postgresql and Blazer to Generate a Cohort Report. But that query wasn’t the right thing to ask. It does highlight one challenge of SQL; it can be hard to test the correctness of your query.