Task
You are given customer-level usage activity for an Elsevier product and a table of customer events such as contract changes or support escalations. Write a PostgreSQL query to identify customers who were active in ClinicalKey in the 30 days before their event date but had no ClinicalKey usage in the 30 days after the same event. Return one row per qualifying event with the customer, event details, pre-event usage count, post-event usage count, days since last pre-event usage, and a churn flag.
Use the event date as the anchor for both windows. Ignore usage from other products. Customers with no pre-event ClinicalKey activity should not appear.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| segment | VARCHAR(50) | Customer segment |
| | |
customer_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| customer_id | INT | Customer tied to the event |
| event_date | DATE | Event date |
| event_type | VARCHAR(50) | Type of event |
| event_reason | VARCHAR(100) | Event reason |
| | |
product_usage
| column | type | description |
|---|
| usage_id | INT | Usage record identifier |
| customer_id | INT | Customer identifier |
| product_name | VARCHAR(100) | Elsevier product used |
| usage_date | DATE | Date of activity |
| sessions | INT | Number of sessions |
Sample data
Representative rows include ClinicalKey activity both before and after events, non-ClinicalKey usage, NULL session values, and customers without matching events.
Expected output
| event_id | customer_id | customer_name | event_type | event_date | pre_event_sessions | post_event_sessions | days_since_last_pre_event_usage | churned_after_event |
|---|
| 101 | 1 | Northside General | contract_change | 2024-03-15 | 8 | 0 | 5 | true |
| 103 | 3 | Lakeshore Health | support_escalation | 2024-03-20 | 8 | 0 | 10 | true |
| 106 | 6 | Metro Research Lab | support_escalation | 2024-03-18 | 7 | 0 | 8 | true |
