Repository navigation
Expand file tree
/
Copy pathsaas_subscription_churn.sql
More file actions
109 lines (98 loc) · 5.65 KB
/
Copy pathsaas_subscription_churn.sql
File metadata and controls
109 lines (98 loc) · 5.65 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
/*
* Dremio "Messy Data" Challenge: SaaS Subscription Churn
*
* Scenario:
* Subscription data.
* 3 Tables: CUSTOMERS (Meta), PLANS (Catalog), SUBSCRIPTIONS (History).
*
* Objective for AI Agent:
* 1. Join Tables: Link SUBSCRIPTIONS to PLANS and CUSTOMERS.
* 2. Fix Overlaps: Dedup active periods (Prioritize higher tier Plan).
* 3. Calc MRR: Sum(Plan_Price) for active accounts.
*/
-------------------------------------------------------------------------------
-- 1. SETUP: Create Raw Folder
-------------------------------------------------------------------------------
CREATE FOLDER IF NOT EXISTS Cloud_SaaS;
CREATE FOLDER IF NOT EXISTS Cloud_SaaS.Billing;
-------------------------------------------------------------------------------
-- 2. DDL: Create Raw Tables (3 Tables)
-------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Cloud_SaaS.Billing.CUSTOMERS (
CUST_ID VARCHAR,
REGION VARCHAR,
SIGNUP_DATE DATE
);
CREATE TABLE IF NOT EXISTS Cloud_SaaS.Billing.PLANS (
PLAN_ID VARCHAR,
PLAN_NAME VARCHAR,
MONTHLY_PRICE DOUBLE,
TIER_LEVEL INT -- 1=Basic, 2=Pro, 3=Ent
);
CREATE TABLE IF NOT EXISTS Cloud_SaaS.Billing.SUBSCRIPTIONS (
SUB_ID VARCHAR,
CUST_ID VARCHAR,
PLAN_ID VARCHAR,
START_DT DATE,
END_DT DATE
);
-------------------------------------------------------------------------------
-- 3. SEED DATA (50+ Records)
-------------------------------------------------------------------------------
-- PLANS
INSERT INTO Cloud_SaaS.Billing.PLANS VALUES
('P-1', 'Basic', 10.0, 1), ('P-2', 'Pro', 50.0, 2), ('P-3', 'Enterprise', 1000.0, 3),
('P-4', 'Legacy', 5.0, 1), ('P-5', 'Pro_2022', 45.0, 2);
-- CUSTOMERS (20 Rows)
INSERT INTO Cloud_SaaS.Billing.CUSTOMERS VALUES
('C-1', 'US', '2023-01-01'), ('C-2', 'EU', '2023-01-01'), ('C-3', 'APAC', '2023-01-01'),
('C-4', 'US', '2023-01-01'), ('C-5', 'US', '2023-01-01'), ('C-6', 'EU', '2023-01-01'),
('C-7', 'US', '2023-01-01'), ('C-8', 'EU', '2023-01-01'), ('C-9', 'US', '2023-01-01'),
('C-10', 'APAC', '2023-01-01'), ('C-11', 'US', '2023-01-01'), ('C-12', 'US', '2023-01-01'),
('C-13', 'EU', '2023-01-01'), ('C-14', 'US', '2023-01-01'), ('C-15', 'US', '2023-01-01'),
('C-16', 'US', '2023-01-01'), ('C-17', 'US', '2023-01-01'), ('C-18', 'US', '2023-01-01'),
('C-19', 'US', '2023-01-01'), ('C-20', 'US', '2023-01-01');
-- SUBSCRIPTIONS (50 Rows)
INSERT INTO Cloud_SaaS.Billing.SUBSCRIPTIONS VALUES
('S-1', 'C-1', 'P-1', '2023-01-01', '2023-01-31'), ('S-2', 'C-1', 'P-1', '2023-02-01', '2023-02-28'),
('S-3', 'C-2', 'P-1', '2023-01-01', '2023-01-31'), ('S-4', 'C-2', 'P-2', '2023-01-15', '2023-02-15'), -- Overlap Upgrade
('S-5', 'C-3', 'P-2', '2023-01-01', '2023-12-31'), ('S-6', 'C-4', 'P-3', '2023-01-01', '2023-12-31'),
('S-7', 'C-5', 'P-1', '2023-01-01', '2023-01-31'), ('S-8', 'C-5', 'P-1', '2023-02-01', '2023-02-28'),
('S-9', 'C-5', 'P-1', '2023-03-01', '2023-03-31'), ('S-10', 'C-6', 'P-2', '2023-01-01', '2023-01-31'),
('S-11', 'C-6', 'P-2', '2023-01-10', '2023-02-10'), ('S-12', 'C-7', 'P-3', '2023-01-01', NULL),
('S-13', 'C-8', 'P-1', NULL, '2023-01-31'), ('S-14', 'C-9', 'P-4', '2023-01-01', '2023-12-31'),
('S-15', 'C-10', 'P-1', '2023-02-01', '2023-01-01'), ('S-16', 'C-11', 'P-2', '2023-01-01', '2023-01-31'),
('S-17', 'C-11', 'P-2', '2023-01-01', '2023-01-31'), ('S-18', 'C-12', 'P-5', '2023-01-01', '2023-12-31'),
('S-19', 'C-13', 'P-2', '2023-01-01', '2023-12-31'), ('S-20', 'C-14', 'P-1', '2023-01-01', '2023-01-15'),
('S-21', 'C-14', 'P-1', '2023-01-16', '2023-01-31'), ('S-22', 'C-15', 'P-3', '2023-01-01', '2023-01-31'),
('S-23', 'C-16', 'P-1', '2023-01-01', '2023-01-31'), ('S-24', 'C-17', 'P-1', '2023-01-01', '2023-01-31'),
('S-25', 'C-18', 'P-1', '2023-01-01', '2023-01-31'), ('S-26', 'C-19', 'P-1', '2023-01-01', '2023-01-31'),
('S-27', 'C-20', 'P-2', '2023-01-01', '2023-12-31'), ('S-28', 'C-20', 'P-2', '2023-01-01', '2023-12-31'),
('S-29', 'C-20', 'P-2', '2023-01-01', '2023-12-31'), ('S-30', 'C-1', 'P-2', '2023-03-01', '2023-03-31'),
('S-31', 'C-2', 'P-3', '2023-03-01', '2023-03-31'), ('S-32', 'C-3', 'P-1', '2024-01-01', '2024-01-31'),
('S-33', 'C-4', 'P-1', '2024-01-01', '2099-12-31'), ('S-34', 'C-5', 'P-1', '2023-04-01', '2023-04-30'),
('S-35', 'C-6', 'P-1', '2023-03-01', '2023-03-31'), ('S-36', 'C-7', 'P-1', '2024-01-01', '2024-01-31'),
('S-37', 'C-8', 'P-2', '2023-02-01', '2023-02-28'), ('S-38', 'C-9', 'P-2', '2024-01-01', '2024-01-31'),
('S-39', 'C-10', 'P-3', '2023-02-01', '2023-02-28'), ('S-40', 'C-11', 'P-2', '2023-02-01', '2023-02-28'),
('S-41', 'C-12', 'P-1', '2024-01-01', '2024-01-31'), ('S-42', 'C-13', 'P-2', '2024-01-01', '2024-01-31'),
('S-43', 'C-14', 'P-1', '2023-02-01', '2023-02-28'), ('S-44', 'C-15', 'P-3', '2023-02-01', '2023-02-28'),
('S-45', 'C-16', 'P-1', '2023-02-01', '2023-02-28'), ('S-46', 'C-17', 'P-1', '2023-02-01', '2023-02-28'),
('S-47', 'C-18', 'P-1', '2023-02-01', '2023-02-28'), ('S-48', 'C-19', 'P-1', '2023-02-01', '2023-02-28'),
('S-49', 'C-20', 'P-2', '2024-01-01', '2024-01-31'), ('S-50', 'C-20', 'P-1', '2025-01-01', '2025-01-31');
-------------------------------------------------------------------------------
-- 4. EXAMPLE AI AGENT PROMPT
-------------------------------------------------------------------------------
/*
* Prompt to Copy/Paste into Dremio AI:
* "Calculate SaaS revenue in Cloud_SaaS.Billing.
*
* 1. Bronze: Raw View of SUBSCRIPTIONS, PLANS, CUSTOMERS.
* 2. Silver:
* - Join: SUBSCRIPTIONS to PLANS on Plan_ID.
* - Clean Logic: If End_DT is NULL, assume Active. Fix End_DT < Start_DT.
* 3. Gold:
* - MRR Report: Sum(Monthly_Price) for currently active subscriptions.
* - Churn List: Customers with past End_DT and no active Subscription.
*
* Show the SQL."
*/