CTE Churn Identification | Dataford Interview Questions

Given a table of user transactions, write a query using Common Table Expressions (CTEs) to identify churned users, defined as users who transacted last month but not this month.

Use the month containing the latest transaction date as this month, and the immediately preceding calendar month as last month. Exclude transactions with a NULL user_id.

Output

Return one row per churned user with the column user_id.
Include users active last month and inactive this month.
Sort by user_id in ascending order.

Schema

user_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Identifier of the user who made the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is CTE Churn Identification. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem