Analyze subscriptions ending in April 2025 and assign each one a renewal status. A subscription is `Ready` when it has a successful payment within the 30 days before its end date and recorded activity within the 14 days before its end date. If only the payment condition is met, label it `Engagement Review`. If only the activity condition is met, label it `Payment Review`. Otherwise, label it `At Risk`. Return the subscription ID, customer name, region, plan name, and renewal status. Sort the results by subscription ID in descending order. ### Table Schema | Table | Column | Type | |---|---|---| | regions | region_id | integer | | regions | region_name | varchar(50) | | users | user_id | integer | | users | user_name | varchar(100) | | users | region_id | integer | | plans | plan_id | integer | | plans | plan_name | varchar(50) | | subscriptions | subscription_id | integer | | subscriptions | user_id | integer | | subscriptions | plan_id | integer | | subscriptions | end_date | date | | payments | payment_id | integer | | payments | subscription_id | integer | | payments | payment_date | date | | payments | payment_status | varchar(20) | | activity_events | event_id | integer | | activity_events | subscription_id | integer | | activity_events | activity_date | date | ### Example Input: subscriptions | subscription_id | user_id | plan_id | end_date | |---:|---:|---:|---| | 1001 | 101 | 10 | 2025-04-10 | | 1002 | 102 | 20 | 2025-04-15 | | 1003 | 103 | 10 | 2025-04-20 | | 1004 | 104 | 20 | 2025-04-25 | | 1005 | 105 | 10 | 2025-04-30 | ### Example Input: payments | payment_id | subscription_id | payment_date | payment_status | |---:|---:|---|---| | 1 | 1001 | 2025-03-11 | success | | 2 | 1001 | 2025-04-11 | failed | | 3 | 1002 | 2025-02-01 | success | | 4 | 1002 | 2025-03-16 | failed | | 5 | 1003 | 2025-03-21 | success | | 6 | 1004 | 2025-03-26 | success | | 7 | 1005 | 2025-03-31 | success | | 8 | 1005 | 2025-04-30 | failed | ### Example Input: activity_events | event_id | subscription_id | activity_date | |---:|---:|---| | 1 | 1001 | 2025-04-01 | | 2 | 1001 | 2025-04-09 | | 3 | 1002 | 2025-04-01 | | 4 | 1003 | 2025-03-21 | | 5 | 1004 | 2025-04-11 | | 6 | 1005 | 2025-04-16 | | 7 | 1005 | 2025-04-30 | ### Example Output Explanation Subscription 1001 has a successful payment exactly 30 days before its end date and recent activity, so it is `Ready`. Subscription 1002 has recent activity but no successful payment in the required payment window, so it is `Payment Review`. Subscription 1003 has a qualifying payment but no recent activity, so it is `Engagement Review`. Subscription 1004 qualifies for both conditions, while subscription 1005 qualifies because its payment is exactly 30 days before its end date and its activity occurs on the end date.
DROP TABLE IF EXISTS activity_events; DROP TABLE IF EXISTS payments; DROP TABLE IF EXISTS subscriptions; DROP TABLE IF EXISTS plans; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS regions; CREATE TABLE regions ( region_id INT, region_name VARCHAR(50) ); CREATE TABLE users ( user_id INT, user_name VARCHAR(100), region_id INT ); CREATE TABLE plans ( plan_id INT, plan_name VARCHAR(50) ); CREATE TABLE subscriptions ( subscription_id INT, user_id INT, plan_id INT, end_date DATE ); CREATE TABLE payments ( payment_id INT, subscription_id INT, payment_date DATE, payment_status VARCHAR(20) ); CREATE TABLE activity_events ( event_id INT, subscription_id INT, activity_date DATE ); INSERT INTO regions VALUES (1, 'North'), (2, 'West'); INSERT INTO users VALUES (101, 'Ava Morgan', 1), (102, 'Ben Carter', 1), (103, 'Cara Lewis', 2), (104, 'Dan Foster', 2), (105, 'Eli Brooks', 1); INSERT INTO plans VALUES (10, 'Basic'), (20, 'Pro'); INSERT INTO subscriptions VALUES (1001, 101, 10, '2025-04-10'), (1002, 102, 20, '2025-04-15'), (1003, 103, 10, '2025-04-20'), (1004, 104, 20, '2025-04-25'), (1005, 105, 10, '2025-04-30'); INSERT INTO payments VALUES (1, 1001, '2025-03-11', 'success'), (2, 1001, '2025-04-11', 'failed'), (3, 1002, '2025-02-01', 'success'), (4, 1002, '2025-03-16', 'failed'), (5, 1003, '2025-03-21', 'success'), (6, 1004, '2025-03-26', 'success'), (7, 1005, '2025-03-31', 'success'), (8, 1005, '2025-04-30', 'failed'); INSERT INTO activity_events VALUES (1, 1001, '2025-04-01'), (2, 1001, '2025-04-09'), (3, 1002, '2025-04-01'), (4, 1003, '2025-03-21'), (5, 1004, '2025-04-11'), (6, 1005, '2025-04-16'), (7, 1005, '2025-04-30');