Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| user_id | integer | User identifier. |
| subscription_type | varchar | Subscription plan. |
| last_active_date | date | Most recent watch date. |
Churn
Risk
Users
Find users who have not watched any content in the last 30 days.
Table
Sample Data
Input
Output
Sample Input: users
| user_id | subscription_type | last_active_date |
|---|---|---|
| 101 | Premium | 2024-01-01 |
| 102 | Basic | 2024-02-20 |
| 103 | Standard | 2024-01-10 |
| 104 | Premium | 2024-02-15 |
| 105 | Basic | 2024-01-20 |
| 106 | Standard | 2024-02-25 |
| 107 | Premium | 2024-01-05 |
Expected Output
| user_id |
|---|
| 101 |
| 103 |
| 105 |
| 107 |
SQL Editor
Run
Query
Waiting for query
| user_id |
|---|
| 101 |
| 103 |
| 105 |
| 107 |
Hints
Unlock
Clues
Hint 01: The table already has each user's last activity.
Hint 02: Inactive means last_active_date is older than 30 days.
Hint 03: Select only user_id.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT user_id FROM users WHERE last_active_date < CURRENT_DATE - INTERVAL '30 days';
Explanation
Step By
Step
01
Read each user's last_active_date.
02
Compare it with the current date minus 30 days.
03
Return users whose last activity is before that cutoff.
Related Questions