Twitter (X) Identifying Inactive Users — SQL

User Retention Analysis

Problem Statement

As a Data Analyst at Twitter (X), you're working with the User Growth and Retention team to identify users who might be at risk of churning. The team wants to create a re-engagement campaign targeting users who were previously active but have become inactive recently. This analysis is crucial for understanding user behavior patterns and implementing proactive retention strategies.

Your task: Identify users who haven't posted any tweets in the last 30 days (as of '2023-03-15') but had posted at least one tweet before that 30-day period. Include their last tweet date, days since last tweet, and total tweets.

Business Context

Twitter's growth team uses inactive user identification for targeted re-engagement campaigns.
Understanding when users become inactive helps optimize retention strategies.
This analysis helps prioritize limited marketing resources on recoverable users.
Inactive user patterns can inform product improvements for user engagement.

Key Requirements

Core Logic: (Current Date Assumption for Analysis: 2023-03-15)

Identify users who haven't tweeted in the last 30 days (i.e., no tweets on or after '2023-02-14').
The identified users must have had at least one tweet before this 30-day cutoff (i.e., at least one tweet before '2023-02-14').
Exclude users who never tweeted at all.
Consider only users with account_status = 'active'.
Consider only original tweets (is_retweet = FALSE).

Output Specification:

user_id: User identifier.
username: User's display name.
last_tweet_date: Date of their most recent tweet (must be before '2023-02-14').
days_since_last_tweet: Number of days from '2023-03-15' to their last_tweet_date.
total_tweets: Total number of original tweets by the user.
Order results by days_since_last_tweet DESC (most inactive first).

Data Quality Considerations:

The solution must inherently handle users with no tweets or only tweets within the last 30 days.
Assume tweet_date >= user join_date for valid tweets.
Assume date calculations are based on the provided '2023-03-15' current date.

Database Schema & Sample Data

Assume the following schema and sample data for Users and Tweets tables (key fields shown):

Table: `Users`

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    join_date DATE NOT NULL,
    account_status VARCHAR(20) DEFAULT 'active'
    -- ... other columns
);

user_id	username	join_date	account_status
1	john_doe	2022-12-01	active
2	jane_smith	2022-12-05	active
3	bob_brown	2022-12-10	active
4	sara_white	2022-12-15	active
5	mike_black	2022-12-20	active
6	lisa_green	2022-12-25	active
7	tom_blue	2023-01-01	active
8	anna_red	2023-01-05	suspended

Table: `Tweets`

CREATE TABLE Tweets (
    tweet_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    tweet_date DATE NOT NULL,
    content TEXT NOT NULL,
    is_retweet BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
    -- ... other columns
);

tweet_id	user_id	tweet_date	content	is_retweet
101	1	2023-01-05	"Hello Twitter!"	FALSE
102	2	2023-01-10	"Just joined, excited!"	FALSE
103	3	2023-01-15	"My first tweet"	FALSE
104	1	2023-02-05	"Another day, another tweet"	FALSE
105	4	2023-01-20	"New here, saying hi!"	FALSE
106	2	2023-02-15	"Beautiful day today"	FALSE
107	5	2023-01-25	"Testing Twitter out"	FALSE
108	6	2023-02-20	"Loving the new features!"	FALSE
109	1	2023-02-25	"Weekend vibes"	FALSE
110	6	2023-02-28	"Almost March already!"	FALSE
111	7	2023-01-30	"Finally posting something"	FALSE
112	2	2023-03-01	"March is here!"	FALSE
113	1	2023-03-02	"Spring is coming"	FALSE

Expected Output (Analysis Date: 2023-03-15):

The 30-day cutoff date is '2023-02-14' (i.e., 2023-03-15 minus 30 days is 2023-02-13, so tweets must be before this date, meaning on or before 2023-02-12. *Correction:* "Last 30 days" means from 2023-02-14 to 2023-03-15 inclusive. So "haven't posted in last 30 days" means last post was *before* 2023-02-14).

user_id	username	last_tweet_date	days_since_last_tweet	total_tweets
7	tom_blue	2023-01-30	44	1
5	mike_black	2023-01-25	49	1
4	sara_white	2023-01-20	54	1
3	bob_brown	2023-01-15	59	1

Note: Users 1, 2, 6 are excluded because they tweeted on/after '2023-02-14'. User 8 is excluded due to 'suspended' status. All listed users had at least one tweet before '2023-02-14' and no tweets on or after '2023-02-14'. Total tweets are all original tweets by the user.

Identifying Previously Active, Now Inactive Users

ADVANCED

Write a PostgreSQL query to identify users who have not posted any original tweets in the last 30 days (from '2023-03-15') but had posted at least one original tweet before that 30-day period. Only consider 'active' users. The output should include user ID, username, their last tweet date, the number of days since that last tweet (from '2023-03-15'), and their total count of original tweets. Order by days since last tweet (descending).

Hint

Let's define the "current date" as '2023-03-15'::DATE and the "30-day cutoff date" as '2023-03-15'::DATE - INTERVAL '30 days' which is '2023-02-13'. A user is inactive in the last 30 days if their last tweet was *before* '2023-02-13' + INTERVAL '1 day' = '2023-02-14'.

User Tweet Summary CTE:
- Start by joining Users with Tweets (LEFT JOIN to keep all users initially, then filter).
- Filter for Users.account_status = 'active' and Tweets.is_retweet = FALSE.
- For each user, calculate:
  - Their overall last tweet date (MAX(tweet_date)).
  - Their total number of original tweets (COUNT(tweet_id)).
  - The date of their last tweet *within* the last 30 days (e.g., MAX(CASE WHEN tweet_date >= '2023-02-14' THEN tweet_date ELSE NULL END)). Let's call this last_tweet_in_30_days.
  - The date of their last tweet *before* the 30-day window (e.g., MAX(CASE WHEN tweet_date < '2023-02-14' THEN tweet_date ELSE NULL END)). Let's call this last_tweet_before_30_days.
- Group by user_id and username.
Filtering for Inactive Users:
- From the CTE above, select users where:
  - last_tweet_in_30_days IS NULL (meaning they had no tweets in the last 30 days).
  - last_tweet_before_30_days IS NOT NULL (meaning they *did* have at least one tweet before the 30-day window).
- The last_tweet_date for the output will be last_tweet_before_30_days from the CTE, as this is their actual last tweet if they are inactive in the recent period.
Calculate Output Fields:
- days_since_last_tweet: '2023-03-15'::DATE - last_tweet_date (which is the last_tweet_before_30_days).
- total_tweets: This was already calculated in the first CTE.
Final Selection and Ordering: Select the required columns (user_id, username, last_tweet_date, days_since_last_tweet, total_tweets) and order them.

Alternative Hint: Another approach could be to calculate each user's overall last tweet date and total tweets. Then, in an outer query or using HAVING, filter these users: their overall last tweet date must be before the 30-day cutoff (< '2023-02-14'), AND they must have a COUNT of tweets > 0 (ensuring they tweeted at least once).

Solution Approach (PostgreSQL)

Imagine you're trying to find Twitter users who used to tweet but have gone quiet recently:

Define "Recent": First, we set a "current date" (e.g., March 15, 2023). "Last 30 days" means the period from February 14, 2023, to March 15, 2023.
Check Each User's Tweets: For every active user:
- Find the date of their very last (original, not retweet) post.
- Count how many (original) posts they've ever made.
Filter for the "Quiet Ones": Now, we only keep users if:
- Their very last post was before February 14, 2023 (meaning they haven't posted anything in the recent 30-day window).
- AND they have made at least one post sometime in the past (so we know they were active before).
Calculate Days Silent: For these quiet users, calculate how many days it's been from our "current date" (March 15, 2023) back to their last post date.
Show Results: Display their ID, username, when they last tweeted, how many days ago that was, and their total tweet count. Sort this list to see who has been silent the longest.

Conceptual Query Structure (using CTEs)

WITH UserTweetSummary AS (
    -- Aggregate tweet data for each active user, focusing on original tweets
    SELECT
        u.user_id,
        u.username,
        MAX(t.tweet_date) AS overall_last_tweet_date,
        COUNT(t.tweet_id) AS total_original_tweets
    FROM
        Users u
    LEFT JOIN
        Tweets t ON u.user_id = t.user_id AND t.is_retweet = FALSE
    WHERE
        u.account_status = 'active'
    GROUP BY
        u.user_id, u.username
)
-- Identify inactive users based on the criteria
SELECT
    uts.user_id,
    uts.username,
    uts.overall_last_tweet_date AS last_tweet_date,
    ('2023-03-15'::DATE - uts.overall_last_tweet_date) AS days_since_last_tweet,
    uts.total_original_tweets AS total_tweets
FROM
    UserTweetSummary uts
WHERE
    uts.total_original_tweets > 0 -- Must have tweeted at least once
    AND uts.overall_last_tweet_date < '2023-03-15'::DATE - INTERVAL '30 days' -- Last tweet was before the 30-day window started
    -- (i.e., last tweet_date < '2023-02-13')
    -- More precisely, if analysis date is '2023-03-15', then 30 days prior is '2023-02-13'.
    -- "Haven't posted in last 30 days" means their last post date is strictly less than ('2023-03-15' - 29 days) = '2023-02-14'.
    -- So, overall_last_tweet_date < '2023-02-14'
ORDER BY
    days_since_last_tweet DESC;

-- Corrected WHERE clause for inactivity:
-- overall_last_tweet_date < ('2023-03-15'::DATE - INTERVAL '29 days') -- which is < '2023-02-14'

(This is a structural example; a detailed solution would refine date logic and conditions.)

Explanation Notes:

1. User Tweet Summary (UserTweetSummary CTE):

Joins Users with Tweets. A LEFT JOIN is used to include all users, and conditions on tweets (is_retweet = FALSE) are applied in the ON clause or a subsequent WHERE if grouping on user attributes only.
Filters for Users.account_status = 'active'.
Calculates MAX(t.tweet_date) to get the most recent original tweet date for each user.
Calculates COUNT(t.tweet_id) to get the total number of original tweets for each user.
Groups by user details to get these aggregates per user.

2. Final Selection and Filtering:

Selects from the UserTweetSummary CTE.
The crucial WHERE clause conditions:
- uts.total_original_tweets > 0: Ensures the user has tweeted at least once in their lifetime.
- uts.overall_last_tweet_date < ('2023-03-15'::DATE - INTERVAL '29 days'): This checks if the user's last tweet date is before the start of the 30-day window. If "today" is 2023-03-15, the 30-day window is [2023-02-14, 2023-03-15]. So, "haven't posted in the last 30 days" means their overall_last_tweet_date must be strictly less than 2023-02-14.
Calculates days_since_last_tweet as ('2023-03-15'::DATE - uts.overall_last_tweet_date).
Orders the results by days_since_last_tweet DESC.

Key Logic Points for a Full Solution:

Defining the 30-day window: If the analysis date is '2023-03-15', the "last 30 days" includes dates from '2023-02-14' up to '2023-03-15'. A user is "inactive in the last 30 days" if their latest tweet occurred *before* '2023-02-14'.
"Previously active" condition: This is met if their overall_last_tweet_date (which must be < '2023-02-14') is not NULL, implying they have tweeted at least once. The total_original_tweets > 0 condition also ensures this.
Filtering is_retweet = FALSE is important to only consider original content.
Filtering account_status = 'active' ensures we don't target suspended users.

Take it Further! What are your thoughts on this problem or alternative ways to solve it? Share your own SQL query attempts or insights in the comments below!

Back to SQL