Lesson 60 of 61

8 mins read

Python SQL Capstone Project: Enterprise Analytics Engine

In Plain English

Build a production-grade relational database from scratch: design normalized tables with Primary and Foreign Keys, configure `CHECK` invariants and default timestamps, create B-Tree indexes on high-frequency search paths, construct secure reporting views, and execute multi-dimensional revenue analytics.

The Mental Model

"Building a complete, standalone database backend for an enterprise SaaS billing system, ready to handle transactions and analytical reporting."

Deep Dive: How It Works

Step 1: Create normalized tables (`customers`, `subscriptions`, `invoices`) with constraints and cascades.

Step 2: Create composite indexes for high-throughput user and date filtering.

Step 3: Construct a secure virtual view (`active_subscriptions_view`) for application consumption.

Step 4: Execute a multi-table analytical query calculating Monthly Recurring Revenue (MRR) by plan tier.

Syntax Blueprint

-- Capstone Pipeline:
1. CREATE TABLE ... WITH CONSTRAINTS
2. CREATE INDEX ... ON FOREIGN KEYS
3. CREATE VIEW ... AS SELECT ...
4. SELECT ... JOIN ... GROUP BY ... HAVING ...

Capstone integrates schema definition, indexing, views, and advanced analytical queries.

Core Rules to Remember

Full Production Architecture: Combines all 9 prior modules into a cohesive database application.

Robust Referential Integrity: Foreign keys with cascades prevent orphan records and maintain consistency.

High-Performance Reporting: Indexes and views provide sub-millisecond analytical query execution.

Common Beginner Traps & How to Fix Them

Mistake:Failing to index foreign key columns in child tables.

Why it happens: Databases create indexes for Primary Keys automatically, but NOT for Foreign Keys.

How to fix: Always write explicit `CREATE INDEX` statements for all foreign key columns.

Pro Tip: Combine views with indexing to create clean, high-performance data access layers for your backend APIs.

Live Interactive Example

Hit Run Code to see it live

Full Capstone SaaS Analytics Pipeline

Python 3.12

PRAGMA foreign_keys = ON;

-- 1. Schema Definition
CREATE TABLE IF NOT EXISTS saas_orgs (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS saas_plans (
  id INTEGER PRIMARY KEY,
  plan_name TEXT NOT NULL UNIQUE,
  monthly_fee REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS saas_subscriptions (
  id INTEGER PRIMARY KEY,
  org_id INTEGER NOT NULL,
  plan_id INTEGER NOT NULL,
  status TEXT DEFAULT 'ACTIVE',
  FOREIGN KEY (org_id) REFERENCES saas_orgs(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES saas_plans(id)
);

-- 2. Seed Data
INSERT INTO saas_orgs (id, name) VALUES (1, 'Vortex AI'), (2, 'Nebula Cloud'), (3, 'Solaris Systems');
INSERT INTO saas_plans (id, plan_name, monthly_fee) VALUES (1, 'Starter', 49.0), (2, 'Enterprise', 499.0);

INSERT INTO saas_subscriptions (org_id, plan_id, status) VALUES
  (1, 2, 'ACTIVE'),
  (2, 1, 'ACTIVE'),
  (3, 2, 'ACTIVE');

-- 3. Analytical MRR Summary
SELECT 
  p.plan_name,
  COUNT(s.id) AS subscriber_count,
  SUM(p.monthly_fee) AS total_mrr
FROM saas_plans p
JOIN saas_subscriptions s ON p.id = s.plan_id
WHERE s.status = 'ACTIVE'
GROUP BY p.id, p.plan_name
ORDER BY total_mrr DESC;

Output Console

Click "Run Code" to view the rendered output.

How it works: Monthly Recurring Revenue aggregated across Enterprise and Starter plans.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Run SaaS Capstone Analytics

Execute the provided analytical MRR query to finish the capstone project.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.