Your question is Detect Post-Event Product Dropoff. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer account name |
| segment | VARCHAR(50) | Customer segment such as Academic, Hospital, or Research |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| customer_id | INT | Customer associated with the event |
| event_date | DATE | Date the event occurred |
| event_type | VARCHAR(50) | Type of event |
| event_reason | VARCHAR(100) | Reason or note associated with the event |
| Column | Type | Description |
|---|---|---|
| usage_idPK | INT | Unique usage record identifier |
| customer_id | INT | Customer generating the usage |
| product_name | VARCHAR(100) | Elsevier product used |
| usage_date | DATE | Date of product activity |
| sessions | INT | Number of sessions recorded for that day |