SQL Query for Customer Retention (Churn)

Identifying which customers are about to leave (churn) is vital for any subscription business. This prompt generates the complex "JOIN" and "GROUP BY" logic required to find them.

The Core Prompt

Write a PostgreSQL query to find "At-Risk" customers. Define "At-Risk" as customers who have not logged in for 30 days but have an active subscription. Join the "users" table with the "logs" table and "subscriptions" table.

Technical Depth

This query uses a "LEFT JOIN" and a "MAX(log_date)" to identify the silent gap in user activity, a standard technique in data analytics.

Usage Tips

• Window Functions: Ask the AI to "use a window function to rank customers by their total lifetime spend."
• Optimization: Ask for "suggestions on which columns to index for this specific query."

Example AI Output

SELECT u.email FROM users u JOIN subscriptions s ON u.id = s.user_id WHERE s.status = 'active' GROUP BY u.id HAVING MAX(u.last_login) < NOW() - INTERVAL '30 days'