SQL queries for calculating Stripe MRR

Calculating Monthly Recurring Revenue (MRR) accurately is crucial for Sequin, which uses it as a primary metric to align its team's focus on growth. Initially, attempts to calculate MRR based on the latest paid invoices led to inaccuracies due to non-subscription items or partial subscription representations. To resolve this, a refined SQL query was developed to align with Stripe's MRR calculation by focusing on active subscription items and their quantities, offering a forward-looking metric that updates with any changes in customer subscriptions. This approach ensures real-time, accurate MRR tracking by considering the most recent subscription items' projected revenue, even before billing occurs. Additionally, for backward-looking reports that require analyzing past revenue, a separate query sums the amounts from paid invoices for subscription line items, reflecting actual revenue earned. These queries provide a robust foundation for businesses to customize their MRR calculations according to specific needs, such as handling multiple currencies or applying discounts and taxes.