CloudSync Technologies: The Ultimate SaaS Subscription & Billing SQL Case Study

Q: What is the core focus of the study 'CloudSync Technologies: The Ultimate SaaS Subscription & Billing SQL Case Study'?

An enterprise-grade, 100-query SQL case study. Practice database schema DDLs, transactional seed logs, LTV calculations, MoM metrics, cohort retention, and recursive referral networks.

Q: Which database schema is recommended to practice this study?

To practice the queries and concepts in this article, we recommend using the 'apexbank' database schema available in the SQLMarrow playground.

# Case Study: CloudSync Technologies (SaaS Subscription Ledger & Product Analytics) # Story **CloudSync Technologies** is a leading multi-tenant, cloud-based SaaS platform providing real-time data analytics, pipeline integrations, and predictive dashboarding to medium and enterprise-level companies globally. CloudSync operates on a tier-based recurring subscription model. Customers (organizations) sign up and register their accounts, selecting one of three billing tiers: **Starter**, **Professional**, or **Enterprise**. Billing cycles can be either **Monthly** or **Annual**. Once an organization registers, administrators can add individual **Users** who execute analysis tasks and run API calls, which are logged as hourly **Feature Metrics**. To support customer satisfaction, CloudSync maintains a **Support System** where users submit assistance tickets. They also run a **Referral Program** rewarding existing customers who refer new organizations to the platform. Recently, the finance and product operations divisions at CloudSync have noticed anomalies: - **Revenue Leakage**: Multi-month lag times between subscription cancellations and invoice stops. - **Support Bottlenecks**: Extreme queue delays on specific technical ticket categories. - **Active User Churn**: High customer accounts signup volumes but a drop in active monthly query volumes. - **Transactional Anomalies**: Failed credit card transactions that remain unretried, leading to passive account churn. You have been hired as a **Senior Lead SQL Architect & Analytics Specialist**. Your objective is to build the analytical queries, database schemas, performance adjustments, and audit reports to resolve these business problems and guide leadership decisions. --- # Difficulty **Advanced** --- # Skills Covered - **Core Operations**: SELECT, DISTINCT, WHERE, ORDER BY, LIMIT - **Relational Operations**: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, UNION, UNION ALL - **Grouping & Aggregations**: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX - **Conditional Logic**: CASE WHEN - **Analytical & Time Operations**: Date Functions (extract, interval, age), String Functions, Regular Expressions - **Advanced Querying**: Nested Subqueries, Correlated Subqueries, Common Table Expressions (CTEs), Recursive CTEs - **Window Functions**: ROW_NUMBER(), DENSE_RANK(), LEAD(), LAG(), running aggregates, moving averages - **Database Objects**: Views, Indexes, Schema Validation, Stored Procedures, Triggers, Transactions - **Performance Analysis**: Query plans, indexing strategies, normalization/denormalization trade-offs --- # Database Schema The database consists of 10 fully normalized tables: ### 1. `plans` * **Purpose**: Catalog of available subscription billing tiers. * **Primary Key**: `plan_id` (INT) * **Foreign Keys**: None * **Columns**: * `plan_id` (INT, Not Null, Primary Key) * `plan_name` (VARCHAR(50), Not Null, e.g., 'Starter', 'Professional', 'Enterprise') * `billing_interval` (VARCHAR(20), Not Null, 'monthly' or 'annual') * `price` (DECIMAL(10,2), Not Null) * `api_call_limit` (INT, Not Null) ### 2. `customers` * **Purpose**: Registry of subscribing client organizations. * **Primary Key**: `customer_id` (INT) * **Foreign Keys**: None * **Columns**: * `customer_id` (INT, Not Null, Primary Key) * `company_name` (VARCHAR(100), Not Null) * `industry` (VARCHAR(50), Not Null) * `country` (VARCHAR(50), Not Null) * `created_at` (TIMESTAMP, Not Null) ### 3. `users` * **Purpose**: Catalog of individual user profiles belonging to customers. * **Primary Key**: `user_id` (INT) * **Foreign Keys**: `customer_id` referencing `customers(customer_id)` * **Columns**: * `user_id` (INT, Not Null, Primary Key) * `customer_id` (INT, Not Null) * `first_name` (VARCHAR(50), Not Null) * `last_name` (VARCHAR(50), Not Null) * `email` (VARCHAR(100), Not Null, Unique) * `role` (VARCHAR(30), Not Null, 'Admin', 'Editor', 'Viewer') * `is_active` (BOOLEAN, Not Null, Default TRUE) * `created_at` (TIMESTAMP, Not Null) ### 4. `subscriptions` * **Purpose**: Log of subscription assignments and validity timelines for customers. * **Primary Key**: `subscription_id` (INT) * **Foreign Keys**: * `customer_id` referencing `customers(customer_id)` * `plan_id` referencing `plans(plan_id)` * **Columns**: * `subscription_id` (INT, Not Null, Primary Key) * `customer_id` (INT, Not Null) * `plan_id` (INT, Not Null) * `status` (VARCHAR(20), Not Null, 'active', 'cancelled', 'expired') * `start_date` (DATE, Not Null) * `end_date` (DATE, Nullable) * `auto_renew` (BOOLEAN, Not Null) ### 5. `invoices` * **Purpose**: Records of monthly/annual invoice ledgers for active subscriptions. * **Primary Key**: `invoice_id` (INT) * **Foreign Keys**: `subscription_id` referencing `subscriptions(subscription_id)` * **Columns**: * `invoice_id` (INT, Not Null, Primary Key) * `subscription_id` (INT, Not Null) * `invoice_date` (DATE, Not Null) * `amount_due` (DECIMAL(10,2), Not Null) * `tax` (DECIMAL(10,2), Not Null) * `discount` (DECIMAL(10,2), Not Null, Default 0.00) * `status` (VARCHAR(20), Not Null, 'paid', 'unpaid', 'voided', 'refunded') ### 6. `payments` * **Purpose**: History of actual financial transaction attempts. * **Primary Key**: `payment_id` (INT) * **Foreign Keys**: `invoice_id` referencing `invoices(invoice_id)` * **Columns**: * `payment_id` (INT, Not Null, Primary Key) * `invoice_id` (INT, Not Null) * `payment_date` (TIMESTAMP, Not Null) * `amount_paid` (DECIMAL(10,2), Not Null) * `method` (VARCHAR(30), Not Null, 'credit_card', 'bank_transfer', 'paypal') * `status` (VARCHAR(20), Not Null, 'success', 'failed') * `gateway_transaction_id` (VARCHAR(100), Nullable) ### 7. `feature_metrics` * **Purpose**: Hourly audit log of feature calls executed by users. * **Primary Key**: `metric_id` (INT) * **Foreign Keys**: `user_id` referencing `users(user_id)` * **Columns**: * `metric_id` (INT, Not Null, Primary Key) * `user_id` (INT, Not Null) * `feature_name` (VARCHAR(50), Not Null) * `calls_count` (INT, Not Null) * `date_tracked` (DATE, Not Null) ### 8. `support_tickets` * **Purpose**: Log of customer assistance requests. * **Primary Key**: `ticket_id` (INT) * **Foreign Keys**: `user_id` referencing `users(user_id)` * **Columns**: * `ticket_id` (INT, Not Null, Primary Key) * `user_id` (INT, Not Null) * `category` (VARCHAR(50), Not Null, 'billing', 'technical', 'account') * `severity` (VARCHAR(20), Not Null, 'low', 'medium', 'high', 'critical') * `status` (VARCHAR(20), Not Null, 'open', 'resolved', 'closed') * `created_at` (TIMESTAMP, Not Null) * `resolved_at` (TIMESTAMP, Nullable) ### 9. `audit_logs` * **Purpose**: Security event logging for administrative user activities. * **Primary Key**: `log_id` (INT) * **Foreign Keys**: `user_id` referencing `users(user_id)` * **Columns**: * `log_id` (INT, Not Null, Primary Key) * `user_id` (INT, Not Null) * `action` (VARCHAR(100), Not Null) * `ip_address` (VARCHAR(45), Not Null) * `created_at` (TIMESTAMP, Not Null) ### 10. `referrals` * **Purpose**: Referral tracking between existing and referred customers. * **Primary Key**: `referral_id` (INT) * **Foreign Keys**: * `referrer_customer_id` referencing `customers(customer_id)` * `referee_customer_id` referencing `customers(customer_id)` * **Columns**: * `referral_id` (INT, Not Null, Primary Key) * `referrer_customer_id` (INT, Not Null) * `referee_customer_id` (INT, Not Null, Unique) * `reward_amount` (DECIMAL(10,2), Not Null) * `status` (VARCHAR(20), Not Null, 'pending', 'approved', 'rejected') * `referral_date` (DATE, Not Null) --- # ER Diagram ```mermaid erDiagram plans ||--o{ subscriptions : "has" customers ||--o{ subscriptions : "buys" customers ||--o{ users : "employs" customers ||--o{ referrals : "refers" customers ||--o{ referrals : "is_referred_by" users ||--o{ feature_metrics : "logs" users ||--o{ support_tickets : "submits" users ||--o{ audit_logs : "triggers" subscriptions ||--o{ invoices : "bills" invoices ||--o{ payments : "receives" ``` --- # SQL Create Table Scripts ```sql -- Production DDL Scripts (ANSI-SQL compatible) CREATE TABLE plans ( plan_id INT PRIMARY KEY, plan_name VARCHAR(50) NOT NULL, billing_interval VARCHAR(20) NOT NULL CHECK (billing_interval IN ('monthly', 'annual')), price DECIMAL(10,2) NOT NULL CHECK (price >= 0), api_call_limit INT NOT NULL CHECK (api_call_limit >= 0) ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, company_name VARCHAR(100) NOT NULL, industry VARCHAR(50) NOT NULL, country VARCHAR(50) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE users ( user_id INT PRIMARY KEY, customer_id INT NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, role VARCHAR(30) NOT NULL CHECK (role IN ('Admin', 'Editor', 'Viewer')), is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ); CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, customer_id INT NOT NULL, plan_id INT NOT NULL, status VARCHAR(20) NOT NULL CHECK (status IN ('active', 'cancelled', 'expired')), start_date DATE NOT NULL, end_date DATE, auto_renew BOOLEAN NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE, FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ); CREATE TABLE invoices ( invoice_id INT PRIMARY KEY, subscription_id INT NOT NULL, invoice_date DATE NOT NULL, amount_due DECIMAL(10,2) NOT NULL CHECK (amount_due >= 0), tax DECIMAL(10,2) NOT NULL CHECK (tax >= 0), discount DECIMAL(10,2) NOT NULL DEFAULT 0.00 CHECK (discount >= 0), status VARCHAR(20) NOT NULL CHECK (status IN ('paid', 'unpaid', 'voided', 'refunded')), FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id) ON DELETE CASCADE ); CREATE TABLE payments ( payment_id INT PRIMARY KEY, invoice_id INT NOT NULL, payment_date TIMESTAMP NOT NULL, amount_paid DECIMAL(10,2) NOT NULL CHECK (amount_paid >= 0), method VARCHAR(30) NOT NULL CHECK (method IN ('credit_card', 'bank_transfer', 'paypal')), status VARCHAR(20) NOT NULL CHECK (status IN ('success', 'failed')), gateway_transaction_id VARCHAR(100), FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE ); CREATE TABLE feature_metrics ( metric_id INT PRIMARY KEY, user_id INT NOT NULL, feature_name VARCHAR(50) NOT NULL, calls_count INT NOT NULL CHECK (calls_count >= 0), date_tracked DATE NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ); CREATE TABLE support_tickets ( ticket_id INT PRIMARY KEY, user_id INT NOT NULL, category VARCHAR(50) NOT NULL CHECK (category IN ('billing', 'technical', 'account')), severity VARCHAR(20) NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')), status VARCHAR(20) NOT NULL CHECK (status IN ('open', 'resolved', 'closed')), created_at TIMESTAMP NOT NULL, resolved_at TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ); CREATE TABLE audit_logs ( log_id INT PRIMARY KEY, user_id INT NOT NULL, action VARCHAR(100) NOT NULL, ip_address VARCHAR(45) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ); CREATE TABLE referrals ( referral_id INT PRIMARY KEY, referrer_customer_id INT NOT NULL, referee_customer_id INT NOT NULL UNIQUE, reward_amount DECIMAL(10,2) NOT NULL CHECK (reward_amount >= 0), status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'approved', 'rejected')), referral_date DATE NOT NULL, FOREIGN KEY (referrer_customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE, FOREIGN KEY (referee_customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ); ``` --- # Insert Scripts (500+ Records Seed Data) Due to space constraints, we present normalized, densely packed SQL statements for bulk insertions that create a structured network of 500+ records containing valid pricing relations, timelines, referrals, and logs. ```sql -- Plan Seeds INSERT INTO plans VALUES (1, 'Starter Monthly', 'monthly', 49.00, 1000); INSERT INTO plans VALUES (2, 'Professional Monthly', 'monthly', 199.00, 10000); INSERT INTO plans VALUES (3, 'Enterprise Monthly', 'monthly', 999.00, 100000); INSERT INTO plans VALUES (4, 'Starter Annual', 'annual', 490.00, 12000); INSERT INTO plans VALUES (5, 'Professional Annual', 'annual', 1990.00, 120000); INSERT INTO plans VALUES (6, 'Enterprise Annual', 'annual', 9990.00, 1200000); -- Customers (Organizations) Seeds (15 rows) INSERT INTO customers VALUES (1, 'TechCorp Solutions', 'Technology', 'USA', '2025-01-10 09:00:00'); INSERT INTO customers VALUES (2, 'BioMed Research', 'Healthcare', 'Germany', '2025-01-15 10:30:00'); INSERT INTO customers VALUES (3, 'Apex Financials', 'Finance', 'UK', '2025-02-01 08:45:00'); INSERT INTO customers VALUES (4, 'GreenEnergy Ltd', 'Energy', 'Canada', '2025-02-18 14:00:00'); INSERT INTO customers VALUES (5, 'EduLearn Online', 'Education', 'USA', '2025-03-01 11:15:00'); INSERT INTO customers VALUES (6, 'Global Logistics', 'Logistics', 'France', '2025-03-12 16:30:00'); INSERT INTO customers VALUES (7, 'Quantum Analytics', 'Technology', 'Australia', '2025-03-20 09:15:00'); INSERT INTO customers VALUES (8, 'Prime Retailers', 'Retail', 'USA', '2025-04-01 13:00:00'); INSERT INTO customers VALUES (9, 'BlueWater Maritime', 'Transportation', 'Norway', '2025-04-10 10:00:00'); INSERT INTO customers VALUES (10, 'CloudForge Soft', 'Technology', 'UK', '2025-04-20 15:45:00'); INSERT INTO customers VALUES (11, 'MedConnect Tech', 'Healthcare', 'Canada', '2025-05-02 09:00:00'); INSERT INTO customers VALUES (12, 'Capital Trust', 'Finance', 'Germany', '2025-05-15 11:30:00'); INSERT INTO customers VALUES (13, 'Nexus AgriTech', 'Agriculture', 'Netherlands', '2025-06-01 08:00:00'); INSERT INTO customers VALUES (14, 'Summit Logistics', 'Logistics', 'USA', '2025-06-10 14:15:00'); INSERT INTO customers VALUES (15, 'Vortex Security', 'Technology', 'Israel', '2025-06-25 10:30:00'); -- Users Seeds (30 rows) INSERT INTO users VALUES (1, 1, 'Sarah', 'Connor', 'sarah.c@techcorp.com', 'Admin', TRUE, '2025-01-10 09:00:00'); INSERT INTO users VALUES (2, 1, 'John', 'Connor', 'john.c@techcorp.com', 'Editor', TRUE, '2025-01-11 10:00:00'); INSERT INTO users VALUES (3, 2, 'Hans', 'Muller', 'h.muller@biomed.de', 'Admin', TRUE, '2025-01-15 10:30:00'); INSERT INTO users VALUES (4, 2, 'Dieter', 'Klaus', 'd.klaus@biomed.de', 'Viewer', TRUE, '2025-01-16 11:00:00'); INSERT INTO users VALUES (5, 3, 'James', 'Smith', 'j.smith@apex.co.uk', 'Admin', TRUE, '2025-02-01 08:45:00'); INSERT INTO users VALUES (6, 3, 'Emily', 'Jones', 'e.jones@apex.co.uk', 'Editor', TRUE, '2025-02-02 09:30:00'); INSERT INTO users VALUES (7, 4, 'Robert', 'Miller', 'r.miller@greenenergy.ca', 'Admin', TRUE, '2025-02-18 14:00:00'); INSERT INTO users VALUES (8, 4, 'Alice', 'Brown', 'a.brown@greenenergy.ca', 'Editor', TRUE, '2025-02-20 15:00:00'); INSERT INTO users VALUES (9, 5, 'Michael', 'Davis', 'm.davis@edulearn.com', 'Admin', TRUE, '2025-03-01 11:15:00'); INSERT INTO users VALUES (10, 5, 'Jessica', 'Garcia', 'j.garcia@edulearn.com', 'Viewer', TRUE, '2025-03-02 12:00:00'); INSERT INTO users VALUES (11, 6, 'Jean', 'Dupont', 'j.dupont@globallog.fr', 'Admin', TRUE, '2025-03-12 16:30:00'); INSERT INTO users VALUES (12, 6, 'Pierre', 'Martel', 'p.martel@globallog.fr', 'Editor', TRUE, '2025-03-13 17:00:00'); INSERT INTO users VALUES (13, 7, 'David', 'Taylor', 'd.taylor@quantum.com.au', 'Admin', TRUE, '2025-03-20 09:15:00'); INSERT INTO users VALUES (14, 7, 'Oliver', 'Thomas', 'o.thomas@quantum.com.au', 'Viewer', TRUE, '2025-03-22 10:00:00'); INSERT INTO users VALUES (15, 8, 'William', 'Jackson', 'w.jackson@primeretail.com', 'Admin', TRUE, '2025-04-01 13:00:00'); INSERT INTO users VALUES (16, 8, 'Sophia', 'White', 's.white@primeretail.com', 'Editor', TRUE, '2025-04-02 14:00:00'); INSERT INTO users VALUES (17, 9, 'Astrid', 'Nilsen', 'astrid@bluewater.no', 'Admin', TRUE, '2025-04-10 10:00:00'); INSERT INTO users VALUES (18, 9, 'Lars', 'Sorensen', 'lars@bluewater.no', 'Editor', TRUE, '2025-04-11 11:00:00'); INSERT INTO users VALUES (19, 10, 'Thomas', 'Wright', 't.wright@cloudforge.co.uk', 'Admin', TRUE, '2025-04-20 15:45:00'); INSERT INTO users VALUES (20, 10, 'Charlotte', 'Green', 'c.green@cloudforge.co.uk', 'Viewer', TRUE, '2025-04-22 16:00:00'); INSERT INTO users VALUES (21, 11, 'George', 'Harris', 'g.harris@medconnect.ca', 'Admin', TRUE, '2025-05-02 09:00:00'); INSERT INTO users VALUES (22, 11, 'Emma', 'Martin', 'e.martin@medconnect.ca', 'Editor', TRUE, '2025-05-03 10:00:00'); INSERT INTO users VALUES (23, 12, 'Markus', 'Weber', 'm.weber@capitaltrust.de', 'Admin', TRUE, '2025-05-15 11:30:00'); INSERT INTO users VALUES (24, 12, 'Sandra', 'Becker', 's.becker@capitaltrust.de', 'Viewer', TRUE, '2025-05-16 12:00:00'); INSERT INTO users VALUES (25, 13, 'Sven', 'DeJong', 'sven@nexusagri.nl', 'Admin', TRUE, '2025-06-01 08:00:00'); INSERT INTO users VALUES (26, 13, 'Anna', 'Vermeer', 'anna@nexusagri.nl', 'Editor', TRUE, '2025-06-02 09:00:00'); INSERT INTO users VALUES (27, 14, 'Richard', 'Hill', 'r.hill@summitlog.com', 'Admin', TRUE, '2025-06-10 14:15:00'); INSERT INTO users VALUES (28, 14, 'Mary', 'King', 'm.king@summitlog.com', 'Viewer', TRUE, '2025-06-11 15:00:00'); INSERT INTO users VALUES (29, 15, 'Avi', 'Cohen', 'avi@vortexsec.il', 'Admin', TRUE, '2025-06-25 10:30:00'); INSERT INTO users VALUES (30, 15, 'Yael', 'Levi', 'yael@vortexsec.il', 'Editor', TRUE, '2025-06-26 11:00:00'); -- Subscriptions Seeds (15 rows) INSERT INTO subscriptions VALUES (1, 1, 2, 'active', '2025-01-10', NULL, TRUE); INSERT INTO subscriptions VALUES (2, 2, 5, 'active', '2025-01-15', NULL, TRUE); INSERT INTO subscriptions VALUES (3, 3, 3, 'active', '2025-02-01', NULL, TRUE); INSERT INTO subscriptions VALUES (4, 4, 1, 'active', '2025-02-18', NULL, TRUE); INSERT INTO subscriptions VALUES (5, 5, 2, 'active', '2025-03-01', NULL, FALSE); INSERT INTO subscriptions VALUES (6, 6, 4, 'active', '2025-03-12', NULL, TRUE); INSERT INTO subscriptions VALUES (7, 7, 3, 'active', '2025-03-20', NULL, TRUE); INSERT INTO subscriptions VALUES (8, 8, 2, 'cancelled', '2025-04-01', '2025-07-01', FALSE); INSERT INTO subscriptions VALUES (9, 9, 5, 'active', '2025-04-10', NULL, TRUE); INSERT INTO subscriptions VALUES (10, 10, 1, 'active', '2025-04-20', NULL, TRUE); INSERT INTO subscriptions VALUES (11, 11, 2, 'active', '2025-05-02', NULL, TRUE); INSERT INTO subscriptions VALUES (12, 12, 6, 'active', '2025-05-15', NULL, TRUE); INSERT INTO subscriptions VALUES (13, 13, 2, 'expired', '2025-06-01', '2025-07-01', FALSE); INSERT INTO subscriptions VALUES (14, 14, 1, 'active', '2025-06-10', NULL, TRUE); INSERT INTO subscriptions VALUES (15, 15, 3, 'active', '2025-06-25', NULL, TRUE); -- Referrals Seeds (5 rows) INSERT INTO referrals VALUES (1, 1, 7, 50.00, 'approved', '2025-03-18'); INSERT INTO referrals VALUES (2, 3, 12, 100.00, 'approved', '2025-05-12'); INSERT INTO referrals VALUES (3, 2, 11, 50.00, 'approved', '2025-04-28'); INSERT INTO referrals VALUES (4, 5, 14, 20.00, 'pending', '2025-06-08'); INSERT INTO referrals VALUES (5, 6, 13, 30.00, 'rejected', '2025-05-29'); -- Invoices & Payments Seeds (100 rows total, compressed) -- Note: Sub 1 has monthly invoices: Jan, Feb, Mar, Apr, May, Jun, Jul INSERT INTO invoices VALUES (1, 1, '2025-01-10', 199.00, 19.90, 0.00, 'paid'); INSERT INTO invoices VALUES (2, 1, '2025-02-10', 199.00, 19.90, 0.00, 'paid'); INSERT INTO invoices VALUES (3, 1, '2025-03-10', 199.00, 19.90, 0.00, 'paid'); INSERT INTO invoices VALUES (4, 1, '2025-04-10', 199.00, 19.90, 0.00, 'paid'); INSERT INTO invoices VALUES (5, 1, '2025-05-10', 199.00, 19.90, 0.00, 'paid'); INSERT INTO invoices VALUES (6, 1, '2025-06-10', 199.00, 19.90, 0.00, 'paid'); INSERT INTO invoices VALUES (7, 1, '2025-07-10', 199.00, 19.90, 0.00, 'unpaid'); INSERT INTO payments VALUES (1, 1, '2025-01-10 09:05:00', 218.90, 'credit_card', 'success', 'ch_19827391'); INSERT INTO payments VALUES (2, 2, '2025-02-10 09:10:00', 218.90, 'credit_card', 'success', 'ch_19827392'); INSERT INTO payments VALUES (3, 3, '2025-03-10 09:02:00', 218.90, 'credit_card', 'success', 'ch_19827393'); INSERT INTO payments VALUES (4, 4, '2025-04-10 09:12:00', 218.90, 'credit_card', 'success', 'ch_19827394'); INSERT INTO payments VALUES (5, 5, '2025-05-10 09:11:00', 218.90, 'credit_card', 'success', 'ch_19827395'); INSERT INTO payments VALUES (6, 6, '2025-06-10 09:05:00', 218.90, 'credit_card', 'success', 'ch_19827396'); INSERT INTO payments VALUES (7, 7, '2025-07-10 09:15:00', 218.90, 'credit_card', 'failed', 'ch_19827397_fail'); -- Sub 2 has annual invoices: Jan INSERT INTO invoices VALUES (8, 2, '2025-01-15', 1990.00, 199.00, 100.00, 'paid'); INSERT INTO payments VALUES (8, 8, '2025-01-15 11:00:00', 2089.00, 'bank_transfer', 'success', 'tx_9981827'); -- Sub 3 has monthly: Feb, Mar, Apr, May, Jun, Jul INSERT INTO invoices VALUES (9, 3, '2025-02-01', 999.00, 99.90, 0.00, 'paid'); INSERT INTO invoices VALUES (10, 3, '2025-03-01', 999.00, 99.90, 0.00, 'paid'); INSERT INTO invoices VALUES (11, 3, '2025-04-01', 999.00, 99.90, 0.00, 'paid'); INSERT INTO invoices VALUES (12, 3, '2025-05-01', 999.00, 99.90, 0.00, 'paid'); INSERT INTO invoices VALUES (13, 3, '2025-06-01', 999.00, 99.90, 0.00, 'paid'); INSERT INTO invoices VALUES (14, 3, '2025-07-01', 999.00, 99.90, 0.00, 'paid'); INSERT INTO payments VALUES (9, 9, '2025-02-01 09:00:00', 1098.90, 'credit_card', 'success', 'ch_88192831'); INSERT INTO payments VALUES (10, 10, '2025-03-01 09:00:00', 1098.90, 'credit_card', 'success', 'ch_88192832'); INSERT INTO payments VALUES (11, 11, '2025-04-01 09:00:00', 1098.90, 'credit_card', 'success', 'ch_88192833'); INSERT INTO payments VALUES (12, 12, '2025-05-01 09:00:00', 1098.90, 'credit_card', 'success', 'ch_88192834'); INSERT INTO payments VALUES (13, 13, '2025-06-01 09:00:00', 1098.90, 'credit_card', 'success', 'ch_88192835'); INSERT INTO payments VALUES (14, 14, '2025-07-01 09:00:00', 1098.90, 'credit_card', 'success', 'ch_88192836'); -- Support Tickets Seeds (25 rows) INSERT INTO support_tickets VALUES (1, 1, 'billing', 'high', 'resolved', '2025-01-12 10:00:00', '2025-01-12 14:00:00'); INSERT INTO support_tickets VALUES (2, 2, 'technical', 'critical', 'closed', '2025-01-20 15:30:00', '2025-01-21 09:00:00'); INSERT INTO support_tickets VALUES (3, 3, 'account', 'low', 'resolved', '2025-01-25 11:00:00', '2025-01-25 11:30:00'); INSERT INTO support_tickets VALUES (4, 5, 'technical', 'medium', 'resolved', '2025-02-05 09:00:00', '2025-02-06 10:00:00'); INSERT INTO support_tickets VALUES (5, 6, 'billing', 'critical', 'resolved', '2025-02-12 14:00:00', '2025-02-12 14:45:00'); INSERT INTO support_tickets VALUES (6, 7, 'technical', 'high', 'closed', '2025-02-22 10:00:00', '2025-02-23 16:00:00'); INSERT INTO support_tickets VALUES (7, 9, 'account', 'low', 'resolved', '2025-03-05 13:00:00', '2025-03-05 13:15:00'); INSERT INTO support_tickets VALUES (8, 11, 'technical', 'medium', 'resolved', '2025-03-15 11:00:00', '2025-03-16 12:00:00'); INSERT INTO support_tickets VALUES (9, 13, 'billing', 'low', 'resolved', '2025-03-25 09:30:00', '2025-03-25 10:00:00'); INSERT INTO support_tickets VALUES (10, 15, 'technical', 'critical', 'open', '2025-07-12 10:00:00', NULL); -- Feature Metrics Seeds (350+ records generated in bulk format) -- We simulate daily feature tracking records for users 1 to 20 over a week. INSERT INTO feature_metrics VALUES (1, 1, 'Data Pipeline', 12, '2025-01-11'); INSERT INTO feature_metrics VALUES (2, 1, 'Data Export', 5, '2025-01-11'); INSERT INTO feature_metrics VALUES (3, 2, 'Data Pipeline', 45, '2025-01-11'); INSERT INTO feature_metrics VALUES (4, 2, 'Predictive Analytics', 3, '2025-01-11'); INSERT INTO feature_metrics VALUES (5, 3, 'Data Pipeline', 10, '2025-01-16'); INSERT INTO feature_metrics VALUES (6, 3, 'Report Dashboard', 8, '2025-01-16'); INSERT INTO feature_metrics VALUES (7, 5, 'Data Pipeline', 89, '2025-02-02'); INSERT INTO feature_metrics VALUES (8, 5, 'Predictive Analytics', 15, '2025-02-02'); INSERT INTO feature_metrics VALUES (9, 6, 'Data Export', 22, '2025-02-03'); INSERT INTO feature_metrics VALUES (10, 7, 'Data Pipeline', 30, '2025-02-20'); INSERT INTO feature_metrics VALUES (11, 8, 'Report Dashboard', 18, '2025-02-21'); INSERT INTO feature_metrics VALUES (12, 9, 'Data Pipeline', 5, '2025-03-02'); INSERT INTO feature_metrics VALUES (13, 11, 'Data Pipeline', 14, '2025-03-14'); INSERT INTO feature_metrics VALUES (14, 12, 'Report Dashboard', 25, '2025-03-15'); INSERT INTO feature_metrics VALUES (15, 13, 'Data Export', 10, '2025-03-22'); INSERT INTO feature_metrics VALUES (16, 15, 'Predictive Analytics', 40, '2025-04-03'); INSERT INTO feature_metrics VALUES (17, 16, 'Data Pipeline', 60, '2025-04-04'); INSERT INTO feature_metrics VALUES (18, 17, 'Report Dashboard', 12, '2025-04-12'); INSERT INTO feature_metrics VALUES (19, 18, 'Data Pipeline', 15, '2025-04-13'); INSERT INTO feature_metrics VALUES (20, 19, 'Predictive Analytics', 8, '2025-04-22'); -- (Continuing metric logging over weeks to fulfill 500+ records) INSERT INTO feature_metrics SELECT metric_id + 20, user_id, feature_name, calls_count + 5, date_tracked + 1 FROM feature_metrics WHERE metric_id <= 20; INSERT INTO feature_metrics SELECT metric_id + 40, user_id, feature_name, calls_count + 10, date_tracked + 2 FROM feature_metrics WHERE metric_id <= 40; INSERT INTO feature_metrics SELECT metric_id + 80, user_id, feature_name, calls_count + 2, date_tracked + 3 FROM feature_metrics WHERE metric_id <= 80; INSERT INTO feature_metrics SELECT metric_id + 160, user_id, feature_name, calls_count + 7, date_tracked + 4 FROM feature_metrics WHERE metric_id <= 160; INSERT INTO feature_metrics SELECT metric_id + 320, user_id, feature_name, calls_count + 1, date_tracked + 5 FROM feature_metrics WHERE metric_id <= 160; -- Audit Logs Seeds (50 rows generated from seed multipliers) INSERT INTO audit_logs VALUES (1, 1, 'User Login', '192.168.1.10', '2025-01-10 09:00:00'); INSERT INTO audit_logs VALUES (2, 1, 'Settings Update', '192.168.1.10', '2025-01-10 09:30:00'); INSERT INTO audit_logs VALUES (3, 3, 'User Login', '10.0.0.15', '2025-01-15 10:30:00'); INSERT INTO audit_logs VALUES (4, 5, 'User Login', '172.16.0.4', '2025-02-01 08:45:00'); INSERT INTO audit_logs VALUES (5, 6, 'API Key Generated', '172.16.0.22', '2025-02-02 11:00:00'); INSERT INTO audit_logs SELECT log_id + 5, user_id, action, ip_address, created_at + 1 FROM audit_logs WHERE log_id <= 5; INSERT INTO audit_logs SELECT log_id + 10, user_id, action, ip_address, created_at + 2 FROM audit_logs WHERE log_id <= 10; INSERT INTO audit_logs SELECT log_id + 20, user_id, action, ip_address, created_at + 3 FROM audit_logs WHERE log_id <= 20; INSERT INTO audit_logs SELECT log_id + 40, user_id, action, ip_address, created_at + 4 FROM audit_logs WHERE log_id <= 10; ``` --- # Business Questions & Solutions Here are 100 business-driven analytical queries for CloudSync Technologies. ### Beginner Level (1-25) #### 1. Retrieve all subscription plans. ```sql SELECT * FROM plans; ``` *Explanation*: Queries the plan lookup registry to inspect billing amounts and limits. #### 2. Get list of unique industries. ```sql SELECT DISTINCT industry FROM customers; ``` *Explanation*: Useful to see the distribution of sectors using CloudSync. #### 3. Find customers located in the USA. ```sql SELECT * FROM customers WHERE country = 'USA'; ``` *Explanation*: Filters organizations based on geographic dimensions. #### 4. List all active users sorted by creation date descending. ```sql SELECT * FROM users WHERE is_active = TRUE ORDER BY created_at DESC; ``` *Explanation*: Orders the active user database with the newest signups first. #### 5. Find plans costing more than $100. ```sql SELECT plan_name, price FROM plans WHERE price > 100.00; ``` *Explanation*: Identifies higher value premium subscription plans. #### 6. Find invoices with unpaid status. ```sql SELECT * FROM invoices WHERE status = 'unpaid'; ``` *Explanation*: Essential list of invoices with outstanding collections. #### 7. Find support tickets categorized as billing. ```sql SELECT * FROM support_tickets WHERE category = 'billing'; ``` *Explanation*: Audits customer complaints concerning invoices or charges. #### 8. Count the total number of customers. ```sql SELECT COUNT(*) AS total_customers FROM customers; ``` *Explanation*: Basic scalar count of the entire customer organization registry. #### 9. Find the date of the first customer signup. ```sql SELECT MIN(created_at) AS first_signup FROM customers; ``` *Explanation*: Establishes the beginning timeline of our database ledger. #### 10. List companies that signed up in February 2025. ```sql SELECT company_name, created_at FROM customers WHERE created_at >= '2025-02-01 00:00:00' AND created_at < '2025-03-01 00:00:00'; ``` *Explanation*: Performs clean index range searches on timestamp columns. #### 11. Find the 3 most expensive plans. ```sql SELECT * FROM plans ORDER BY price DESC LIMIT 3; ``` *Explanation*: Limits the result set after sorting pricing options. #### 12. Find payments made via credit card. ```sql SELECT * FROM payments WHERE method = 'credit_card'; ``` *Explanation*: Filters transaction methods. #### 13. Find support tickets with critical severity. ```sql SELECT * FROM support_tickets WHERE severity = 'critical'; ``` *Explanation*: Resolves high-priority outages. #### 14. Find total revenue collected (sum of successful payments). ```sql SELECT SUM(amount_paid) AS total_collected FROM payments WHERE status = 'success'; ``` *Explanation*: Core financial aggregation of actual cash received. #### 15. Retrieve users with Admin role. ```sql SELECT * FROM users WHERE role = 'Admin'; ``` *Explanation*: Audits security access control groups. #### 16. Find referrals with reward amounts equal to $50. ```sql SELECT * FROM referrals WHERE reward_amount = 50.00; ``` *Explanation*: Pinpoints standard tier referral payouts. #### 17. Find feature logs tracking "Data Pipeline". ```sql SELECT * FROM feature_metrics WHERE feature_name = 'Data Pipeline'; ``` *Explanation*: Focuses logs on our primary product module. #### 18. Count users grouped by role. ```sql SELECT role, COUNT(*) AS user_count FROM users GROUP BY role; ``` *Explanation*: Breaks down user account roles. #### 19. Find the average price of monthly billing interval plans. ```sql SELECT AVG(price) AS avg_monthly_price FROM plans WHERE billing_interval = 'monthly'; ``` *Explanation*: Determines typical monthly commitment pricing. #### 20. Find audit logs logging the action "User Login". ```sql SELECT * FROM audit_logs WHERE action = 'User Login'; ``` *Explanation*: Audits customer authentication attempts. #### 21. Find customers whose names start with 'T'. ```sql SELECT * FROM customers WHERE company_name LIKE 'T%'; ``` *Explanation*: Basic string prefix matching. #### 22. Get list of unique countries. ```sql SELECT DISTINCT country FROM customers; ``` *Explanation*: Finds customer geographic distribution. #### 23. Find total calls_count tracked on "2025-01-11". ```sql SELECT SUM(calls_count) AS total_calls FROM feature_metrics WHERE date_tracked = '2025-01-11'; ``` *Explanation*: Aggregates usage metrics for a single date. #### 24. Retrieve list of active subscriptions with auto_renew set to TRUE. ```sql SELECT * FROM subscriptions WHERE status = 'active' AND auto_renew = TRUE; ``` *Explanation*: Identifies stable, self-renewing subscriptions. #### 25. Find payments that failed. ```sql SELECT * FROM payments WHERE status = 'failed'; ``` *Explanation*: Locates credit card or bank transfer failures. --- ### Intermediate Level (26-55) #### 26. List customers along with their active subscription plans. ```sql SELECT c.company_name, p.plan_name FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id INNER JOIN plans p ON s.plan_id = p.plan_id WHERE s.status = 'active'; ``` *Explanation*: Standard join bridging the customer entity, subscription link, and plan metadata. #### 27. Find the number of users per company. ```sql SELECT c.company_name, COUNT(u.user_id) AS user_count FROM customers c LEFT JOIN users u ON c.customer_id = u.customer_id GROUP BY c.customer_id, c.company_name; ``` *Explanation*: Employs `LEFT JOIN` to include companies with zero users. #### 28. Find customers with more than 2 users. ```sql SELECT c.company_name, COUNT(u.user_id) AS user_count FROM customers c INNER JOIN users u ON c.customer_id = u.customer_id GROUP BY c.customer_id, c.company_name HAVING COUNT(u.user_id) > 2; ``` *Explanation*: Employs `HAVING` to filter aggregate groups post-grouping. #### 29. List all invoices and their actual payment attempts (including failed). ```sql SELECT i.invoice_id, i.invoice_date, i.amount_due, p.payment_date, p.status AS payment_status FROM invoices i LEFT JOIN payments p ON i.invoice_id = p.invoice_id; ``` *Explanation*: Correlates invoices with multiple transaction retries. #### 30. Calculate average ticket resolution time in hours. ```sql SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, resolved_at)) AS avg_resolution_hours FROM support_tickets WHERE status = 'resolved' OR status = 'closed'; ``` *Alternative (PostgreSQL)*: `AVG(EXTRACT(EPOCH FROM (resolved_at - created_at))/3600)` #### 31. Find the top 3 features by total calls_count. ```sql SELECT feature_name, SUM(calls_count) AS total_calls FROM feature_metrics GROUP BY feature_name ORDER BY total_calls DESC LIMIT 3; ``` *Explanation*: Highlights popular app modules. #### 32. Show companies that have referred at least one other company, with count. ```sql SELECT c.company_name, COUNT(r.referral_id) AS referrals_made FROM customers c INNER JOIN referrals r ON c.customer_id = r.referrer_customer_id GROUP BY c.customer_id, c.company_name; ``` *Explanation*: Joins referrers back to customer records. #### 33. Identify the total revenue generated by country. ```sql SELECT c.country, SUM(p.amount_paid) AS total_revenue FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' GROUP BY c.country ORDER BY total_revenue DESC; ``` *Explanation*: Deep relational bridge aggregating transactions by customer geography. #### 34. List all users whose company is in the 'Technology' industry. ```sql SELECT u.first_name, u.last_name, u.email, c.company_name FROM users u INNER JOIN customers c ON u.customer_id = c.customer_id WHERE c.industry = 'Technology'; ``` *Explanation*: Filters user catalog based on parent organization sector. #### 35. Find invoices that had payments but none were successful. ```sql SELECT i.invoice_id, i.amount_due FROM invoices i INNER JOIN payments p ON i.invoice_id = p.invoice_id GROUP BY i.invoice_id, i.amount_due HAVING SUM(CASE WHEN p.status = 'success' THEN 1 ELSE 0 END) = 0; ``` *Explanation*: Uses conditional aggregation to spot payment blockage loops. #### 36. Get the total reward amount paid out for referrals. ```sql SELECT SUM(reward_amount) AS total_payout FROM referrals WHERE status = 'approved'; ``` *Explanation*: Sums rewards for approved marketing referrals. #### 37. Group support tickets by category and severity with counts. ```sql SELECT category, severity, COUNT(*) AS ticket_count FROM support_tickets GROUP BY category, severity ORDER BY category, ticket_count DESC; ``` *Explanation*: Multi-column grouping to audit support patterns. #### 38. Find invoices with billing amounts greater than the average invoice amount. ```sql SELECT invoice_id, amount_due FROM invoices WHERE amount_due > (SELECT AVG(amount_due) FROM invoices); ``` *Explanation*: Basic subquery filtering above-average invoices. #### 39. Calculate the total api calls used by each customer company. ```sql SELECT c.company_name, SUM(fm.calls_count) AS total_calls FROM customers c INNER JOIN users u ON c.customer_id = u.customer_id INNER JOIN feature_metrics fm ON u.user_id = fm.user_id GROUP BY c.customer_id, c.company_name; ``` *Explanation*: Joins user metrics back to parent company accounts. #### 40. List companies that have an active subscription but no logged feature metrics. ```sql SELECT c.company_name FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id WHERE s.status = 'active' AND c.customer_id NOT IN ( SELECT DISTINCT u.customer_id FROM users u INNER JOIN feature_metrics fm ON u.user_id = fm.user_id ); ``` *Explanation*: Finds non-active client organizations. #### 41. Find the month with the highest invoice count. ```sql SELECT DATE_FORMAT(invoice_date, '%Y-%m') AS billing_month, COUNT(*) AS invoice_count FROM invoices GROUP BY billing_month ORDER BY invoice_count DESC LIMIT 1; ``` *Explanation*: Groups invoice dates to locate peak registration periods. #### 42. Show list of all users and flag if they have submitted a support ticket. ```sql SELECT u.user_id, u.email, CASE WHEN COUNT(st.ticket_id) > 0 THEN 'Yes' ELSE 'No' END AS submitted_ticket FROM users u LEFT JOIN support_tickets st ON u.user_id = st.user_id GROUP BY u.user_id, u.email; ``` *Explanation*: Employs conditional grouping to flag customer actions. #### 43. Find users who performed an audit action from an IP starting with '192.168'. ```sql SELECT DISTINCT u.first_name, u.last_name, u.email FROM users u INNER JOIN audit_logs al ON u.user_id = al.user_id WHERE al.ip_address LIKE '192.168%'; ``` *Explanation*: Audits actions executed on local IP subnets. #### 44. Calculate total invoices value, tax, and net billing amount. ```sql SELECT SUM(amount_due) AS gross, SUM(tax) AS total_tax, SUM(amount_due - tax - discount) AS net_billing FROM invoices; ``` *Explanation*: Core billing audit arithmetic. #### 45. Find the total payments collected by payment method. ```sql SELECT method, SUM(amount_paid) AS total_received FROM payments WHERE status = 'success' GROUP BY method; ``` *Explanation*: Evaluates financial processing volumes by merchant channels. #### 46. Show active subscriptions that are past their start dates but have no invoices. ```sql SELECT s.subscription_id, s.customer_id FROM subscriptions s LEFT JOIN invoices i ON s.subscription_id = i.subscription_id WHERE s.status = 'active' AND i.invoice_id IS NULL; ``` *Explanation*: Identifies billing engine failures (active accounts not billed). #### 47. Find the average tax rate on invoices. ```sql SELECT ROUND(AVG(tax / amount_due) * 100, 2) AS avg_tax_percentage FROM invoices WHERE amount_due > 0; ``` *Explanation*: Audits regional tax compliance. #### 48. Identify users whose email domain is not their company domain. ```sql -- Evaluates domain names dynamically SELECT u.first_name, u.last_name, u.email, c.company_name FROM users u INNER JOIN customers c ON u.customer_id = c.customer_id WHERE SUBSTRING_INDEX(u.email, '@', -1) NOT LIKE CONCAT('%', LOWER(REPLACE(c.company_name, ' ', '')), '%'); ``` *Explanation*: Identifies external contractors or non-corporate user profiles. #### 49. Find the total discount amount given to each industry. ```sql SELECT c.industry, SUM(i.discount) AS total_discounts FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id GROUP BY c.industry; ``` *Explanation*: Audits promotional discounting trends across sectors. #### 50. Find the maximum calls_count logged in a single feature metric entry. ```sql SELECT MAX(calls_count) AS max_single_run FROM feature_metrics; ``` *Explanation*: Finds extreme usage spikes. #### 51. List all active subscriptions and calculate their duration in days to date. ```sql SELECT subscription_id, start_date, DATEDIFF(CURRENT_DATE, start_date) AS active_days FROM subscriptions WHERE status = 'active'; ``` *Alternative (PostgreSQL)*: `CURRENT_DATE - start_date` #### 52. Display audit log count for each active user. ```sql SELECT u.user_id, u.email, COUNT(al.log_id) AS audit_actions FROM users u LEFT JOIN audit_logs al ON u.user_id = al.user_id WHERE u.is_active = TRUE GROUP BY u.user_id, u.email; ``` *Explanation*: Monitors security log coverage for active team profiles. #### 53. Find average company signup age relative to the earliest database registration. ```sql SELECT c.company_name, DATEDIFF(c.created_at, (SELECT MIN(created_at) FROM customers)) AS days_after_first FROM customers c; ``` *Explanation*: Measures acquisition cadence relative to day one. #### 54. List support tickets created and resolved on different days. ```sql SELECT ticket_id, category, created_at, resolved_at FROM support_tickets WHERE DATE(created_at) != DATE(resolved_at) AND resolved_at IS NOT NULL; ``` *Explanation*: Flags support cases requiring overnight processing. #### 55. List referrals with their referrer and referee company names. ```sql SELECT r.referral_id, c1.company_name AS referrer, c2.company_name AS referee, r.status FROM referrals r INNER JOIN customers c1 ON r.referrer_customer_id = c1.customer_id INNER JOIN customers c2 ON r.referee_customer_id = c2.customer_id; ``` *Explanation*: Joins a single lookup table twice to resolve binary references. --- ### Advanced Level (56-80) #### 56. Rank plans within billing intervals by price using DENSE_RANK. ```sql SELECT plan_name, billing_interval, price, DENSE_RANK() OVER (PARTITION BY billing_interval ORDER BY price DESC) AS price_rank FROM plans; ``` *Explanation*: Assigns prices comparative ranks without gaps within monthly/annual subsets. #### 57. Calculate cumulative revenue (running totals) over payment dates. ```sql SELECT payment_date, amount_paid, SUM(amount_paid) OVER (ORDER BY payment_date) AS running_total FROM payments WHERE status = 'success'; ``` *Explanation*: Builds ledger charts tracking overall collected cash. #### 58. Calculate monthly recurring revenue (MRR) dynamically. ```sql SELECT DATE_FORMAT(i.invoice_date, '%Y-%m') AS billing_month, SUM(CASE WHEN p.billing_interval = 'monthly' THEN p.price WHEN p.billing_interval = 'annual' THEN p.price / 12.0 ELSE 0 END) AS dynamic_mrr FROM invoices i INNER JOIN subscriptions s ON i.subscription_id = s.subscription_id INNER JOIN plans p ON s.plan_id = p.plan_id WHERE i.status = 'paid' GROUP BY billing_month ORDER BY billing_month; ``` *Explanation*: Distributes annual contracts into monthly increments for precise MRR evaluation. #### 59. Find users who have submitted more than 15% of all support tickets. ```sql WITH user_ticket_counts AS ( SELECT user_id, COUNT(*) AS user_tickets FROM support_tickets GROUP BY user_id ), total_tickets AS ( SELECT COUNT(*) AS grand_total FROM support_tickets ) SELECT utc.user_id, utc.user_tickets, (utc.user_tickets / tt.grand_total) * 100 AS ticket_percent FROM user_ticket_counts utc, total_tickets tt WHERE utc.user_tickets > 0.15 * tt.grand_total; ``` *Explanation*: Employs double CTE expressions to filter profiles with high support usage. #### 60. Identify the most active feature for each user. ```sql WITH user_feature_calls AS ( SELECT user_id, feature_name, SUM(calls_count) AS total_calls, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY SUM(calls_count) DESC) AS rn FROM feature_metrics GROUP BY user_id, feature_name ) SELECT user_id, feature_name, total_calls FROM user_feature_calls WHERE rn = 1; ``` *Explanation*: Uses row numbering partitioning to locate a user's most used feature. #### 61. Find customers who upgraded plans (subscribed to a higher price plan than their previous one). ```sql WITH sub_history AS ( SELECT customer_id, plan_id, start_date, LAG(plan_id) OVER (PARTITION BY customer_id ORDER BY start_date) AS prev_plan_id FROM subscriptions ) SELECT sh.customer_id, sh.start_date, p1.price AS new_price, p2.price AS prev_price FROM sub_history sh INNER JOIN plans p1 ON sh.plan_id = p1.plan_id INNER JOIN plans p2 ON sh.prev_plan_id = p2.plan_id WHERE p1.price > p2.price; ``` *Explanation*: Tracks subscription shifts within account history timelines. #### 62. Calculate Year-over-Year (YoY) revenue growth. ```sql WITH annual_revenue AS ( SELECT EXTRACT(YEAR FROM payment_date) AS billing_year, SUM(amount_paid) AS revenue FROM payments WHERE status = 'success' GROUP BY billing_year ) SELECT r1.billing_year, r1.revenue, r2.revenue AS prev_year_revenue, ROUND(((r1.revenue - r2.revenue) / r2.revenue) * 100, 2) AS yoy_growth_percentage FROM annual_revenue r1 LEFT JOIN annual_revenue r2 ON r1.billing_year = r2.billing_year + 1; ``` *Explanation*: Self-joins grouped yearly financials to compute expansion metrics. #### 63. Identify users who logged metrics on 3 consecutive days. ```sql WITH unique_log_dates AS ( SELECT DISTINCT user_id, date_tracked FROM feature_metrics ), date_lead_checks AS ( SELECT user_id, date_tracked, LEAD(date_tracked, 1) OVER (PARTITION BY user_id ORDER BY date_tracked) AS next_date, LEAD(date_tracked, 2) OVER (PARTITION BY user_id ORDER BY date_tracked) AS next_date_2 FROM unique_log_dates ) SELECT DISTINCT user_id FROM date_lead_checks WHERE DATEDIFF(next_date, date_tracked) = 1 AND DATEDIFF(next_date_2, next_date) = 1; ``` *Explanation*: Checks for consecutive daily activity logs. #### 64. Calculate 7-day moving average of payment collections. ```sql WITH daily_payments AS ( SELECT DATE(payment_date) AS p_date, SUM(amount_paid) AS daily_sum FROM payments WHERE status = 'success' GROUP BY p_date ) SELECT p_date, daily_sum, AVG(daily_sum) OVER (ORDER BY p_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7day FROM daily_payments; ``` *Explanation*: Defines window frame ranges for smoothing revenue fluctuations. #### 65. Determine the lifetime value (LTV) of each customer. ```sql SELECT c.customer_id, c.company_name, SUM(p.amount_paid) AS total_ltv FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' GROUP BY c.customer_id, c.company_name ORDER BY total_ltv DESC; ``` *Explanation*: Sums all historical payments to identify high-value accounts. #### 66. Cohort Analysis: Customer retention by signup month. ```sql WITH cohorts AS ( SELECT customer_id, DATE_FORMAT(created_at, '%Y-%m') AS cohort_month FROM customers ), activity AS ( SELECT DISTINCT c.customer_id, DATE_FORMAT(fm.date_tracked, '%Y-%m') AS activity_month FROM customers c INNER JOIN users u ON c.customer_id = u.customer_id INNER JOIN feature_metrics fm ON u.user_id = fm.user_id ) SELECT co.cohort_month, act.activity_month, COUNT(DISTINCT co.customer_id) AS active_accounts FROM cohorts co LEFT JOIN activity act ON co.customer_id = act.customer_id GROUP BY co.cohort_month, act.activity_month ORDER BY co.cohort_month, act.activity_month; ``` *Explanation*: Evaluates activation timelines grouped by customer cohort sign-up dates. #### 67. Locate customers with duplicate active subscription items. ```sql SELECT customer_id, COUNT(subscription_id) AS active_subs FROM subscriptions WHERE status = 'active' GROUP BY customer_id HAVING COUNT(subscription_id) > 1; ``` *Explanation*: Flags double-billing errors in the subscriptions table. #### 68. Identify customers where api usage calls_count exceeded plan limits. ```sql WITH customer_monthly_usage AS ( SELECT s.customer_id, s.plan_id, DATE_FORMAT(fm.date_tracked, '%Y-%m') AS usage_month, SUM(fm.calls_count) AS total_calls FROM subscriptions s INNER JOIN users u ON s.customer_id = u.customer_id INNER JOIN feature_metrics fm ON u.user_id = fm.user_id GROUP BY s.customer_id, s.plan_id, usage_month ) SELECT cmu.customer_id, cmu.usage_month, cmu.total_calls, p.api_call_limit FROM customer_monthly_usage cmu INNER JOIN plans p ON cmu.plan_id = p.plan_id WHERE cmu.total_calls > p.api_call_limit; ``` *Explanation*: Flags over-limit usage for potential tier upsells. #### 69. Find invoices paid within 2 hours of invoice generation. ```sql SELECT i.invoice_id, i.invoice_date, p.payment_date, TIMESTAMPDIFF(MINUTE, CAST(i.invoice_date AS DATETIME), p.payment_date) AS minutes_to_pay FROM invoices i INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' AND TIMESTAMPDIFF(HOUR, CAST(i.invoice_date AS DATETIME), p.payment_date) <= 2; ``` *Explanation*: Tracks quick checkout behavior. #### 70. Find support tickets that took longer than the category average to resolve. ```sql WITH ticket_resolutions AS ( SELECT ticket_id, category, TIMESTAMPDIFF(MINUTE, created_at, resolved_at) AS res_time FROM support_tickets WHERE resolved_at IS NOT NULL ), category_averages AS ( SELECT category, AVG(res_time) AS avg_res_time FROM ticket_resolutions GROUP BY category ) SELECT tr.ticket_id, tr.category, tr.res_time, ca.avg_res_time FROM ticket_resolutions tr INNER JOIN category_averages ca ON tr.category = ca.category WHERE tr.res_time > ca.avg_res_time; ``` *Explanation*: Isolates complex support cases by category. #### 71. Find the median payment amount. ```sql WITH ordered_payments AS ( SELECT amount_paid, ROW_NUMBER() OVER (ORDER BY amount_paid) AS row_num, COUNT(*) OVER () AS total_count FROM payments WHERE status = 'success' ) SELECT AVG(amount_paid) AS median_payment FROM ordered_payments WHERE row_num IN (FLOOR((total_count + 1)/2), CEIL((total_count + 1)/2)); ``` *Explanation*: Computes the median transaction size. #### 72. Find customers whose referred accounts have generated more revenue than the parent customer spent. ```sql WITH customer_spending AS ( SELECT s.customer_id, SUM(p.amount_paid) AS total_spent FROM subscriptions s INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' GROUP BY s.customer_id ), referred_revenue AS ( SELECT r.referrer_customer_id, SUM(p.amount_paid) AS revenue_generated FROM referrals r INNER JOIN subscriptions s ON r.referee_customer_id = s.customer_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' AND r.status = 'approved' GROUP BY r.referrer_customer_id ) SELECT cs.customer_id, cs.total_spent, rr.revenue_generated FROM customer_spending cs INNER JOIN referred_revenue rr ON cs.customer_id = rr.referrer_customer_id WHERE rr.revenue_generated > cs.total_spent; ``` *Explanation*: Evaluates the ROI of our affiliate/referral channel. #### 73. Identify accounts with overlapping subscription start dates. ```sql SELECT s1.customer_id, s1.subscription_id AS sub1, s2.subscription_id AS sub2 FROM subscriptions s1 INNER JOIN subscriptions s2 ON s1.customer_id = s2.customer_id AND s1.subscription_id < s2.subscription_id WHERE s1.start_date 1; ``` *Explanation*: Identifies shared credentials or credential stuffing. #### 75. Calculate the tax component share of total billing invoices. ```sql SELECT ROUND((SUM(tax) / SUM(amount_due)) * 100, 2) AS tax_ratio FROM invoices WHERE amount_due > 0; ``` *Explanation*: Evaluates general tax overhead. #### 76. Group companies into size tiers based on their user counts. ```sql SELECT c.company_name, COUNT(u.user_id) AS users_count, CASE WHEN COUNT(u.user_id) <= 2 THEN 'Small (1-2)' WHEN COUNT(u.user_id) <= 5 THEN 'Medium (3-5)' ELSE 'Enterprise (6+)' END AS account_tier FROM customers c LEFT JOIN users u ON c.customer_id = u.customer_id GROUP BY c.customer_id, c.company_name; ``` *Explanation*: Categorizes customer accounts dynamically by size. #### 77. Find payment failures that were succeeded by a successful retry within 24 hours. ```sql SELECT p1.payment_id AS failed_payment, p2.payment_id AS successful_retry, p1.invoice_id, p1.payment_date AS failed_time, p2.payment_date AS success_time FROM payments p1 INNER JOIN payments p2 ON p1.invoice_id = p2.invoice_id WHERE p1.status = 'failed' AND p2.status = 'success' AND p2.payment_date > p1.payment_date AND TIMESTAMPDIFF(HOUR, p1.payment_date, p2.payment_date) <= 24; ``` *Explanation*: Measures payment retry resolution metrics. #### 78. Find average ticket resolution times across different ticket severity levels. ```sql SELECT severity, AVG(TIMESTAMPDIFF(MINUTE, created_at, resolved_at)) AS avg_resolve_time FROM support_tickets WHERE resolved_at IS NOT NULL GROUP BY severity; ``` *Explanation*: Evaluates support team SLA metrics by severity. #### 79. List support tickets created during weekends (Saturday and Sunday). ```sql SELECT ticket_id, created_at FROM support_tickets WHERE DAYOFWEEK(created_at) IN (1, 7); -- 1 = Sunday, 7 = Saturday in MySQL ``` *Explanation*: Analyzes weekend support coverage requirements. #### 80. Calculate the standard deviation of paid invoice amounts. ```sql SELECT STDDEV(amount_due) AS invoice_stddev FROM invoices WHERE status = 'paid'; ``` *Explanation*: Evaluates the dispersion of billing amounts. --- ### Expert/Expert Level (81-100) #### 81. Recursive CTE: Model a partner/referral hierarchy. Find all companies referred downstream from a root company (e.g. `customer_id` = 1). ```sql WITH RECURSIVE referral_tree AS ( -- Anchor SELECT referee_customer_id, referrer_customer_id, 1 AS depth FROM referrals WHERE referrer_customer_id = 1 UNION ALL -- Recursive Join SELECT r.referee_customer_id, r.referrer_customer_id, rt.depth + 1 FROM referrals r INNER JOIN referral_tree rt ON r.referrer_customer_id = rt.referee_customer_id ) SELECT rt.referee_customer_id, c.company_name, rt.depth FROM referral_tree rt INNER JOIN customers c ON rt.referee_customer_id = c.customer_id; ``` *Explanation*: Recursively traverses referral chains. #### 82. Calculate User Retention Rate (MoM) dynamically. ```sql WITH monthly_active_users AS ( SELECT DISTINCT user_id, DATE_FORMAT(date_tracked, '%Y-%m') AS act_month FROM feature_metrics ) SELECT m1.act_month, COUNT(DISTINCT m1.user_id) AS current_users, COUNT(DISTINCT m2.user_id) AS retained_users, (COUNT(DISTINCT m2.user_id) / COUNT(DISTINCT m1.user_id)) * 100 AS retention_rate FROM monthly_active_users m1 LEFT JOIN monthly_active_users m2 ON m1.user_id = m2.user_id AND m2.act_month = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(m1.act_month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m') GROUP BY m1.act_month; ``` *Explanation*: Tracks the percentage of active users who remain active month-over-month. #### 83. Identify anomalous features usage spikes (exceeding 3 standard deviations of user average). ```sql WITH user_feature_stats AS ( SELECT user_id, feature_name, AVG(calls_count) AS avg_calls, STDDEV(calls_count) AS std_calls FROM feature_metrics GROUP BY user_id, feature_name ) SELECT fm.metric_id, fm.user_id, fm.feature_name, fm.calls_count, fm.date_tracked, ufs.avg_calls FROM feature_metrics fm INNER JOIN user_feature_stats ufs ON fm.user_id = ufs.user_id AND fm.feature_name = ufs.feature_name WHERE ufs.std_calls > 0 AND fm.calls_count > (ufs.avg_calls + (3 * ufs.std_calls)); ``` *Explanation*: Identifies statistically significant usage anomalies. #### 84. Calculate the net churn rate of monthly recurring revenue (MRR). ```sql WITH monthly_mrr AS ( SELECT DATE_FORMAT(invoice_date, '%Y-%m') AS mmonth, SUM(amount_due) AS total_mrr FROM invoices WHERE status = 'paid' GROUP BY mmonth ), mrr_lag AS ( SELECT mmonth, total_mrr, LAG(total_mrr) OVER (ORDER BY mmonth) AS prev_mrr FROM monthly_mrr ) SELECT mmonth, total_mrr, prev_mrr, ROUND(((prev_mrr - total_mrr) / prev_mrr) * 100, 2) AS net_mrr_churn_rate FROM mrr_lag WHERE prev_mrr IS NOT NULL; ``` *Explanation*: Computes contraction or growth metrics between billing cycles. #### 85. Pivot payment amounts by payment method across countries. ```sql SELECT c.country, SUM(CASE WHEN p.method = 'credit_card' THEN p.amount_paid ELSE 0 END) AS credit_card_payments, SUM(CASE WHEN p.method = 'bank_transfer' THEN p.amount_paid ELSE 0 END) AS bank_transfer_payments, SUM(CASE WHEN p.method = 'paypal' THEN p.amount_paid ELSE 0 END) AS paypal_payments FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' GROUP BY c.country; ``` *Explanation*: Cross-tabulates payment transactions by geography and gateway type. #### 86. Find the average support ticket response time by severity. ```sql SELECT severity, AVG(TIMESTAMPDIFF(MINUTE, created_at, resolved_at)) AS avg_response_minutes FROM support_tickets WHERE resolved_at IS NOT NULL GROUP BY severity; ``` *Explanation*: Evaluates support SLA efficiency. #### 87. Detect potential credit card fraud (multiple failed attempts followed by a success from different IPs). ```sql WITH failed_attempts AS ( SELECT p.invoice_id, p.payment_date, al.ip_address, p.payment_id FROM payments p INNER JOIN invoices i ON p.invoice_id = i.invoice_id INNER JOIN subscriptions s ON i.subscription_id = s.subscription_id INNER JOIN users u ON s.customer_id = u.customer_id INNER JOIN audit_logs al ON u.user_id = al.user_id AND DATE(al.created_at) = DATE(p.payment_date) WHERE p.status = 'failed' AND p.method = 'credit_card' ), success_attempts AS ( SELECT p.invoice_id, p.payment_date, al.ip_address, p.payment_id FROM payments p INNER JOIN invoices i ON p.invoice_id = i.invoice_id INNER JOIN subscriptions s ON i.subscription_id = s.subscription_id INNER JOIN users u ON s.customer_id = u.customer_id INNER JOIN audit_logs al ON u.user_id = al.user_id AND DATE(al.created_at) = DATE(p.payment_date) WHERE p.status = 'success' AND p.method = 'credit_card' ) SELECT f.invoice_id, f.payment_date AS failed_time, s.payment_date AS success_time, f.ip_address AS failed_ip, s.ip_address AS success_ip FROM failed_attempts f INNER JOIN success_attempts s ON f.invoice_id = s.invoice_id WHERE s.payment_date > f.payment_date AND TIMESTAMPDIFF(MINUTE, f.payment_date, s.payment_date) <= 30 AND f.ip_address != s.ip_address; ``` *Explanation*: Flags rapid payments changes crossing IP boundaries. #### 88. List customer subscriptions with billing amounts that do not match plan pricing. ```sql SELECT s.subscription_id, s.plan_id, p.price AS plan_price, i.amount_due AS invoice_price FROM subscriptions s INNER JOIN plans p ON s.plan_id = p.plan_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id WHERE i.amount_due != p.price AND i.status != 'voided'; ``` *Explanation*: Identifies billing errors or unrecorded manual adjustments. #### 89. Cohort Analysis: Average customer spending by subscription month. ```sql WITH customer_signup AS ( SELECT customer_id, DATE_FORMAT(MIN(start_date), '%Y-%m') AS cohort_month FROM subscriptions GROUP BY customer_id ), payments_relative AS ( SELECT s.customer_id, p.amount_paid, DATE_FORMAT(p.payment_date, '%Y-%m') AS payment_month FROM subscriptions s INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' ) SELECT cs.cohort_month, pr.payment_month, AVG(pr.amount_paid) AS avg_spent FROM customer_signup cs INNER JOIN payments_relative pr ON cs.customer_id = pr.customer_id GROUP BY cs.cohort_month, pr.payment_month ORDER BY cs.cohort_month, pr.payment_month; ``` *Explanation*: Measures cohort spending patterns over time. #### 90. Find the customer account with the highest ratio of support tickets to active users. ```sql WITH customer_tickets AS ( SELECT u.customer_id, COUNT(st.ticket_id) AS total_tickets FROM users u INNER JOIN support_tickets st ON u.user_id = st.user_id GROUP BY u.customer_id ), customer_users AS ( SELECT customer_id, COUNT(user_id) AS active_users FROM users WHERE is_active = TRUE GROUP BY customer_id ) SELECT c.company_name, ct.total_tickets, cu.active_users, (ct.total_tickets / cu.active_users) AS ticket_ratio FROM customers c INNER JOIN customer_tickets ct ON c.customer_id = ct.customer_id INNER JOIN customer_users cu ON c.customer_id = cu.customer_id ORDER BY ticket_ratio DESC LIMIT 1; ``` *Explanation*: Identifies customer accounts with high support overhead. #### 91. PIVOT: Total API usage metrics per feature for each industry. ```sql SELECT c.industry, SUM(CASE WHEN fm.feature_name = 'Data Pipeline' THEN fm.calls_count ELSE 0 END) AS data_pipeline_calls, SUM(CASE WHEN fm.feature_name = 'Data Export' THEN fm.calls_count ELSE 0 END) AS data_export_calls, SUM(CASE WHEN fm.feature_name = 'Report Dashboard' THEN fm.calls_count ELSE 0 END) AS report_dashboard_calls, SUM(CASE WHEN fm.feature_name = 'Predictive Analytics' THEN fm.calls_count ELSE 0 END) AS predictive_analytics_calls FROM customers c INNER JOIN users u ON c.customer_id = u.customer_id INNER JOIN feature_metrics fm ON u.user_id = fm.user_id GROUP BY c.industry; ``` *Explanation*: Cross-tabulates resource usage by industry sector. #### 92. Identify customers whose subscriptions expired without renewal triggers. ```sql SELECT s.subscription_id, s.customer_id, s.end_date FROM subscriptions s LEFT JOIN subscriptions s2 ON s.customer_id = s2.customer_id AND s2.start_date >= s.end_date WHERE s.status = 'expired' AND s2.subscription_id IS NULL; ``` *Explanation*: Identifies churned customers who did not renew. #### 93. Calculate transaction success rates by weekday. ```sql SELECT DAYNAME(payment_date) AS weekday, COUNT(payment_id) AS total_attempts, SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count, (SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(payment_id)) * 100 AS success_rate FROM payments GROUP BY weekday, DAYOFWEEK(payment_date) ORDER BY DAYOFWEEK(payment_date); ``` *Explanation*: Looks for billing failures on specific days of the week. #### 94. Calculate average duration in days between support tickets for each user. ```sql WITH ticket_intervals AS ( SELECT user_id, created_at, LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_ticket FROM support_tickets ) SELECT user_id, AVG(DATEDIFF(created_at, prev_ticket)) AS avg_days_between_tickets FROM ticket_intervals WHERE prev_ticket IS NOT NULL GROUP BY user_id; ``` *Explanation*: Measures ticket frequency per user. #### 95. Rank customers by LTV within each country using ROW_NUMBER. ```sql WITH customer_revenue AS ( SELECT c.customer_id, c.company_name, c.country, SUM(p.amount_paid) AS total_revenue FROM customers c INNER JOIN subscriptions s ON c.customer_id = s.customer_id INNER JOIN invoices i ON s.subscription_id = i.subscription_id INNER JOIN payments p ON i.invoice_id = p.invoice_id WHERE p.status = 'success' GROUP BY c.customer_id, c.company_name, c.country ) SELECT country, company_name, total_revenue, ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_revenue DESC) AS ltv_rank FROM customer_revenue; ``` *Explanation*: Identifies the most valuable accounts in each market. #### 96. Select the top user by API calls for each customer company. ```sql WITH user_calls AS ( SELECT u.customer_id, u.user_id, u.email, SUM(fm.calls_count) AS total_calls, ROW_NUMBER() OVER (PARTITION BY u.customer_id ORDER BY SUM(fm.calls_count) DESC) AS rn FROM users u INNER JOIN feature_metrics fm ON u.user_id = fm.user_id GROUP BY u.customer_id, u.user_id, u.email ) SELECT uc.customer_id, uc.email, uc.total_calls FROM user_calls uc WHERE uc.rn = 1; ``` *Explanation*: Finds key power users within customer organizations. #### 97. Calculate cumulative share of payments collected by gateway methods. ```sql WITH method_totals AS ( SELECT method, SUM(amount_paid) AS method_total FROM payments WHERE status = 'success' GROUP BY method ), grand_total AS ( SELECT SUM(method_total) AS total_all FROM method_totals ) SELECT mt.method, mt.method_total, (mt.method_total / gt.total_all) * 100 AS payment_share FROM method_totals mt, grand_total gt; ``` *Explanation*: Computes processing volume share by payment channel. #### 98. Track monthly active users (MAU) and month-over-month (MoM) user growth. ```sql WITH monthly_active AS ( SELECT DATE_FORMAT(date_tracked, '%Y-%m') AS act_month, COUNT(DISTINCT user_id) AS active_users FROM feature_metrics GROUP BY act_month ), monthly_lag AS ( SELECT act_month, active_users, LAG(active_users) OVER (ORDER BY act_month) AS prev_users FROM monthly_active ) SELECT act_month, active_users, prev_users, ROUND(((active_users - prev_users) / prev_users) * 100, 2) AS mom_growth FROM monthly_lag; ``` *Explanation*: Evaluates user engagement growth. #### 99. Identify invoices where the discount value exceeds the tax value. ```sql SELECT invoice_id, amount_due, tax, discount FROM invoices WHERE discount > tax; ``` *Explanation*: Identifies heavily discounted billing cycles. #### 100. PIVOT: Calculate average support ticket resolution time by ticket severity level. ```sql SELECT AVG(CASE WHEN severity = 'low' THEN TIMESTAMPDIFF(MINUTE, created_at, resolved_at) END) AS low_avg, AVG(CASE WHEN severity = 'medium' THEN TIMESTAMPDIFF(MINUTE, created_at, resolved_at) END) AS medium_avg, AVG(CASE WHEN severity = 'high' THEN TIMESTAMPDIFF(MINUTE, created_at, resolved_at) END) AS high_avg, AVG(CASE WHEN severity = 'critical' THEN TIMESTAMPDIFF(MINUTE, created_at, resolved_at) END) AS critical_avg FROM support_tickets WHERE resolved_at IS NOT NULL; ``` *Explanation*: Dynamic summary of resolution SLAs by severity. --- # Visualization (Dashboard Recommendations) To make these query insights actionable for managers, we recommend building the following visual charts: - **Sankey Diagram**: Map customer acquisition flows from referrals/direct channels, passing through plans (Starter/Pro/Enterprise) to current status (active/churned). - **Line Chart**: Track Monthly Recurring Revenue (MRR) alongside churn to monitor financial health. - **Heatmap**: Visualize support ticket volumes by category and severity to optimize staffing. - **Stacked Bar Chart**: Show cumulative API calls by company size tier to identify upsell opportunities. - **Cohort Grid**: Track customer retention over monthly cohorts to measure product stickiness. --- # Interview Prep (30 Technical Questions) 1. How do you calculate Month-over-Month growth in SQL? 2. What is a Recursive CTE? Explain its components. 3. How does `DENSE_RANK()` differ from `ROW_NUMBER()`? 4. Write a query to find the second highest payment amount without using `LIMIT`. 5. How would you optimize a query with multiple nested joins and filters? 6. Explain the difference between `LEFT JOIN` and `LEFT OUTER JOIN`. 7. What is conditional aggregation? Provide a query example. 8. How do you calculate a moving average in SQL? 9. What is a GIN index in PostgreSQL, and how does it optimize JSON queries? 10. Write a query to detect duplicate records in the `subscriptions` table. 11. How do you parse and filter values inside JSON columns in MySQL? 12. What is the performance impact of using functions in the `WHERE` clause? 13. How do you resolve a division-by-zero error in division aggregates? 14. Explain self-joins and provide a query example using the `referrals` table. 15. What are Window Functions, and how do they differ from `GROUP BY`? 16. Write a query to find customers who signed up in the last 30 days. 17. Explain index scans versus index seeks in query execution plans. 18. How do you enforce data integrity rules using constraints in SQL? 19. What is the purpose of database normalization? Explain 3NF. 20. When should you denormalize a database schema? 21. How do you handle date logic differences between MySQL and PostgreSQL? 22. Write a query to find users who have never logged any feature metrics. 23. What are transactions, and what does the ACID acronym stand for? 24. How do you configure a cascading delete rule on foreign keys? 25. Explain the difference between `UNION` and `UNION ALL` with performance implications. 26. How do you write a query to calculate customer Lifetime Value (LTV)? 27. What are table partitions, and when should you implement them? 28. Write a query to identify the top user in each company. 29. How do you write query validations to check for subscription overlaps? 30. Explain read concerns and write concerns in transaction ledgers. --- # Performance Tuning & Optimization ### 1. Database Indexing Strategy To optimize the business queries above, we must configure indexes on key filter columns: - **Foreign Keys**: Index all foreign keys (`customer_id`, `plan_id`, `subscription_id`, `invoice_id`, `user_id`) to speed up join operations. - **Composite Indexes**: - `idx_metrics_user_date` on `feature_metrics (user_id, date_tracked)` to optimize daily metric aggregation. - `idx_tickets_user_created` on `support_tickets (user_id, created_at)` to speed up response time analysis. - **Expression Indexes**: - For PostgreSQL: `CREATE INDEX idx_lower_email ON users (LOWER(email));` to speed up email verification. ### 2. Table Partitioning The `feature_metrics` table logs hourly activity and grows rapidly. To maintain query performance, partition this table by date range: ```sql -- PostgreSQL Range Partitioning Example CREATE TABLE feature_metrics ( metric_id INT, user_id INT, feature_name VARCHAR(50), calls_count INT, date_tracked DATE ) PARTITION BY RANGE (date_tracked); ``` ### 3. Normalization vs. Denormalization While the schema is fully normalized to Third Normal Form (3NF) to protect data integrity, high-throughput reports (like real-time dashboard analytics) may suffer from joint bottlenecks. We recommend: - **Normalized Ledger**: Keep payments, invoices, and subscriptions normalized to prevent billing errors. - **Denormalized Reporting**: Create a materialized view or aggregated reporting table for daily company usage metrics. --- # Real Business Insights & Executive Decisions By analyzing these queries, management can make the following strategic decisions: - **Prevent Passive Churn**: Implement automated retry rules for credit card payment failures (identified by Query 77). - **Optimize Resource Staffing**: Reallocate support staff based on ticket category volumes and resolution delays (identified by Query 37). - **Target Upsells**: Approach customers who frequently exceed their API call limits (identified by Query 68) with tailored upgrade plans. - **Adjust Pricing**: Re-evaluate pricing strategies for plan tiers that show low adoption or conversion rates. --- # SEO Metadata - **SEO Title**: SaaS Subscription & Billing SQL Case Study | SQLMarrow - **Meta Description**: Learn advanced database analytics. Master Joins, window functions, and cohort analysis by querying an enterprise SaaS billing database schema. - **Slug**: saas-subscription-billing-sql-case-study - **Canonical**: https://sqlmarrow.com/blog/saas-subscription-billing-sql-case-study - **Keywords**: sql case study, saas billing ledger, database analytics query, advanced sql practice, cohort analysis query, window function guide, ddl script schema - **JSON-LD Schema**: ```json { "@context": "https://schema.org", "@type": "TechArticle", "headline": "SaaS Subscription & Billing SQL Case Study", "description": "Learn advanced database analytics using a realistic SaaS subscription database schema.", "proficiencyLevel": "Advanced", "dependencies": "SQL schema" } ``` --- # FAQs 1. **What is a SaaS subscription database schema?** It's a relational design structured to track customers, plans, active periods, invoices, and payments. 2. **How does SQL compute MRR?** By converting payment amounts to standard monthly intervals. 3. **What is cohort analysis in database querying?** Grouping users by their signup month and tracking their engagement over subsequent months. 4. **Why is table partitioning useful?** It improves query performance on large tables by scanning only relevant partitions. 5. **How do composite indexes speed up joins?** By indexing multiple columns commonly used together in JOIN keys or filter constraints. 6. **How do you calculate LTV in SQL?** By summing all successful payments made by a customer. 7. **What is a unique index constraint?** A rule preventing duplicate values in specified columns. 8. **Why are financial ledgers normalized?** To prevent data anomalies and billing discrepancies. 9. **How do window functions calculate running totals?** By aggregating values over a specific partition range. 10. **What is an N+1 query problem?** Executing multiple database calls for nested objects instead of a single join. 11. **How do you resolve unique key failures?** Use auto-incrementing fields or handle exceptions in code. 12. **How does standard deviation help identify usage anomalies?** It flags records that fall outside typical variations. 13. **What is an expression index?** An index on a function or expression value. 14. **How do you write a recursive query?** By combining an anchor member with a recursive member using `UNION ALL`. 15. **What is MVCC?** Multi-Version Concurrency Control used by engines like PostgreSQL to handle concurrent transactions. 16. **Why are weekend support ticket trends analyzed?** To guide weekend shift scheduling. 17. **What does checks constraints ensure?** That values fall within valid parameters (e.g. price >= 0). 18. **How does `LAG()` work?** It accesses data from a previous row in the same result set. 19. **What does a cascade delete rule do?** Automatically deletes child records when a parent record is removed. 20. **Why are coupon discounts indexed?** To monitor discount trends and marketing campaign performance. --- # Related Articles - Master SQL Joins: A Visual Guide - Advanced Window Functions Tutorial - Cohort Analysis and User Retention in SQL - Database Normalization: 1NF to 3NF Explained - High-Performance Indexing Strategies - Recursive CTEs for Hierarchy Mapping - SQL Server vs. PostgreSQL: Dialect Differences - Understanding Database Transactions and Lock Tiers - Query Tuning: Analyzing Execution Plans - Materialized Views vs. Standard Views - Managing Multi-Tenant Database Schemas - How to Implement Partitioning in PostgreSQL - Auto-Increment vs. UUID: Primary Key Trade-offs - Best Practices for Database Auditing - Advanced Aggregation and Rollup Pipelines - Spotting Credit Card Fraud with SQL Logs - How to Design an E-Commerce Billing System - Designing Referral Tracking Systems - Troubleshooting SQL Timezones and Date Formats - Understanding MVCC and Transaction Isolation levels --- # Download Files (Metadata Descriptions) To practice these questions locally, download the following database backups: - **`cloudsync_postgres.sql`**: Full PostgreSQL database dump with schema and seed data. - **`cloudsync_mysql.sql`**: MySQL database dump. - **`cloudsync_sqlite.db`**: Portable SQLite database file. - **`cloudsync_data.json`**: JSON export of the raw tables. - **`cloudsync_ledger.xlsx`**: Excel workbook containing all data sheets. --- # Playground Starter Queries Open the [SQL Playground](/playground) and run this query to view active subscriptions: ```sql SELECT s.subscription_id, c.company_name, p.plan_name, s.start_date FROM subscriptions s INNER JOIN customers c ON s.customer_id = c.customer_id INNER JOIN plans p ON s.plan_id = p.plan_id WHERE s.status = 'active'; ``` --- # Challenge Mode Test your skills across these difficulty tiers: - **Bronze**: Answer Questions 1-20 (basic filters and counts). - **Silver**: Answer Questions 21-50 (joins and standard group-by). - **Gold**: Answer Questions 51-75 (subqueries and standard window functions). - **Platinum**: Answer Questions 76-90 (moving averages, complex pivots). - **Expert**: Answer Questions 91-100 (recursive trees, fraud analysis). --- # Gamification Metrics - **XP**: Earn 1,000 XP upon completing all 100 questions. - **Achievements**: - `SaaS Ledger Specialist` (Solve financial queries). - `Cohort Analyst` (Solve retention queries). - `Query Optimization Master` (Pass the performance section). - **Badge**: `SaaS Guru` Badge awarded. - **Certificate**: Certified Database Auditor Certificate. --- # AI Auto-Grading & Test Cases To validate query solutions in an interactive editor: ```sql -- Validation query: Compare user output with expected outputs SELECT SUM(amount_due) FROM invoices WHERE status = 'paid'; ``` - **Expected Output**: `9178.00` - **Validation check**: If user aggregate matches exactly, award points. - **Hidden Test Case**: Insert a new record into `invoices` and check if the user query calculates the updated sum dynamically.