codeseed
Sign in
Explore Projects
SQLadvancedbackendAI generated

Subscription SaaS Recurring Revenue Reporting

Model a SaaS billing schema with plans, subscriptions, invoices, and cancellations, then write queries computing MRR, churn rate, and cohort retention over time. Learners practice date-bucketed aggregation, LAG/LEAD window functions, and cohort-analysis query patterns.

Estimate
~13h
Steps
6
Completed by
0
Proposed by
codeseed.app

PostgreSQL · pgAdmin

Sign in to claim this project

Project roadmap

  1. 01

    Design the schema

    ~2h

    Create plans, subscriptions, invoices, and cancellations tables.

  2. 02

    Insert multi-year sample data

    ~2h

    Populate several years of subscription, invoice, and cancellation history.

  3. 03

    Compute monthly recurring revenue

    ~2.5h

    Write a date-bucketed query computing MRR for each month.

  4. 04

    Compute month-over-month churn

    ~2.5h

    Use LAG to compare active subscriber counts between consecutive months and derive churn rate.

  5. 05

    Build a cohort retention query

    ~2.5h

    Group customers by signup month and track retention percentage over subsequent months.

  6. 06

    Expose a reporting view

    ~1.5h

    Create a view combining the key SaaS metrics for downstream reporting tools.

Resources

Ready to build this?

Get a GitHub repo and start building. Your AI reviewer checks each step as you go.

~13h · 6 steps

Tech stack

PostgreSQLpgAdmin