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.
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 |
Representative rows include ClinicalKey activity both before and after events, non-ClinicalKey usage, NULL session values, and customers without matching events.
| 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 |
| 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 |
| customer_id | customer_name | segment |
|---|---|---|
| 4 | Summit University | Academic |
| 1 | Northside General | Hospital |
| 7 | Pinecrest Medical Center | |
| 3 | Lakeshore Health | Hospital |
| 2 | Westfield University | Academic |
| 8 | Harbor Clinic | Hospital |
| 6 | Metro Research Lab | Research |
| 5 | Redwood Institute | Research |
| 1 | Granite Bay Medical | Government |
| 2 | Elmwood Institute | Corporate |
| 3 | Willowbrook University | Healthcare System |
| 4 | Harbor Clinic | Corporate |
| 5 | Capital Teaching Hospital | Teaching Hospital |
| 6 | Willowbrook University | SMB |
| 7 | Riverview University | Teaching Hospital |
| 8 | Parkview Health Network | Specialty Clinic |
| 9 | Lakeshore Health | Enterprise |
| 10 | Brighton Medical School | Medical School |
| 11 | Capital Teaching Hospital | Corporate |
| 12 | Capital Teaching Hospital | University System |
| 13 | Hillcrest University | Regional Hospital |
| 14 | Lakeshore Health | null |
| 15 | Queensbridge Medical Center | Enterprise |
| 16 | Cedar Valley Hospital | null |
| 17 | Valley Regional Hospital | Pharma |
| 18 | Riverbend Health System | Life Sciences |
| usage_id | customer_id | product_name | usage_date | sessions |
|---|---|---|---|---|
| 1009 | 3 | ClinicalKey | 2024-03-10 | 3 |
| 1002 | 1 | ClinicalKey | 2024-03-01 | 5 |
| 1018 | 6 | ClinicalKey | 2024-03-10 | 4 |
| 1014 | 4 | ClinicalKey | 2024-03-05 | 6 |
| 1011 | 3 | Scopus | 2024-03-25 | 4 |
| 1005 | 2 | ClinicalKey | 2024-03-05 | 4 |
| 1021 | 7 | ClinicalKey | 2024-03-10 | 0 |
| 1020 | 6 | ClinicalKey | 2024-03-28 | 2 |
| 1007 | 2 | ClinicalKey | 2024-03-20 | 5 |
| 1010 | 3 | ClinicalKey | 2024-02-25 | 5 |
| 1016 | 5 | Scopus | 2024-02-20 | 7 |
| 1023 | 8 | ClinicalKey | 2024-03-26 | 1 |
| 1003 | 1 | ClinicalKey | 2024-03-30 | 2 |
| 1013 | 4 | ClinicalKey | 2024-02-20 | 2 |
| 1015 | 4 | ClinicalKey | 2024-03-12 | 3 |
| 1012 | 4 | ClinicalKey | 2024-02-10 | 1 |
| 1006 | 2 | ClinicalKey | 2024-02-20 | |
| 1019 | 6 | ClinicalKey | 2024-02-28 | 3 |
| 1022 | 8 | ClinicalKey | 2024-03-01 | 2 |
| 1001 | 1 | ClinicalKey | 2024-02-20 | 3 |
| 1008 | 2 | Embase | 2024-02-28 | 6 |
| 1017 | 5 | ClinicalKey | 2024-03-10 | 4 |
| 1004 | 1 | Scopus | 2024-03-10 | 7 |
| 1 | 39 | Embase | 2024-03-06 | 65 |
| 2 | 41 | Knovel | 2024-03-28 | 44 |
| 3 | 49 | Interfolio | 2024-02-25 | 70 |
| event_id | customer_id | event_date | event_type | event_reason |
|---|---|---|---|---|
| 104 | 4 | 2024-03-10 | contract_change | budget_review |
| 101 | 1 | 2024-03-15 | contract_change | renewal_delay |
| 108 | 8 | 2024-03-25 | support_escalation | |
| 103 | 3 | 2024-03-20 | support_escalation | access_issue |
| 102 | 2 | 2024-03-12 | support_escalation | training_gap |
| 107 | 7 | 2024-03-14 | contract_change | downgrade_request |
| 106 | 6 | 2024-03-18 | support_escalation | integration_issue |
| 105 | 5 | 2024-03-05 | contract_change | pricing_change |
| 1 | 5 | 2024-03-23 | upgrade_request | training_gap |
| 2 | 36 | 2024-03-18 | seat_reduction | implementation_delay |
| 3 | 68 | 2024-03-15 | support_escalation | duplicate_tools |
| 4 | 23 | 2024-03-04 | feedback_submission | billing_question |
| 5 | 3 | 2024-03-03 | onboarding_checkin | license_reduction |
| 6 | 8 | 2024-03-19 | seat_expansion | null |
| 7 | 44 | 2024-03-10 | training_request | null |
| 8 | 15 | 2024-03-21 | feedback_submission | authentication_issue |
| 9 | 18 | 2024-03-23 | service_outage | workflow_change |
| 10 | 32 | 2024-03-08 | seat_reduction | null |
| 11 | 87 | 2024-03-08 | onboarding_checkin | missing_entitlements |
| 12 | 31 | 2024-03-20 | integration_issue | team_restructure |
| 13 | 48 | 2024-03-15 | support_followup | high_cost |
| 14 | 77 | 2024-03-18 | service_outage | duplicate_tools |
| 15 | 45 | 2024-03-06 | seat_expansion | platform_confusion |
| 16 | 55 | 2024-03-19 | support_followup | renewal_delay |
| 17 | 35 | 2024-03-26 | billing_dispute | pilot_expired |
| 18 | 51 | 2024-03-25 | contract_change | access_issue |
| 19 | 42 | 2024-03-04 | trial_end | training_gap |
| 20 | 66 | 2024-03-04 | contract_change | low_adoption |
| 21 | 2 | 2024-03-10 | billing_dispute | duplicate_tools |
| 22 | 57 | 2024-03-15 | trial_end | pricing_change |
| 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 |
|---|---|---|---|---|---|---|---|---|
| 103 | 3 | Lakeshore Health | support_escalation | 2024-03-20 | 8 | 0 | 10 | True |
| 1 | 5 | Redwood Institute | upgrade_request | 2024-03-23 | 4 | 0 | 13 | True |
