Calculate MRR from a subscriptions table using SQL

What's really being asked

This question tests your ability to translate a core business metric, Monthly Recurring Revenue (MRR), into a precise SQL query. Interviewers are evaluating your SQL proficiency with date functions and conditional logic, your understanding of subscription data models, and your ability to reason about business logic and edge cases, like handling different plan types (monthly vs. annual).

The full answer

First, a clear definition of an 'active' subscription for the given month. This is any subscription that started on or before the last day of the month AND ends after the first day of the month (or has a null end date). Second, a SQL query that implements this logic in the WHERE clause. Third, the correct aggregation, which is a SUM of prices. Fourth, for the follow-up, it uses a CASE statement to handle different plan types, typically dividing an annual price by 12. A top-tier answer also asks clarifying questions about the schema, like whether the monthly_price for an annual plan is the total annual cost and how to handle null end_date values.

The mistakes people make

A major red flag is writing a WHERE clause that only captures subscriptions starting in the current month (WHERE start_date BETWEEN '...' AND '...'). This calculates new MRR, not total MRR. Another common mistake is failing to handle subscriptions that span the entire month but don't start or end within it. For the annual plan part, simply ignoring the plan_type or dividing all prices by 12 shows a lack of attention to detail. Not asking clarifying questions about ambiguous columns or date logic is also a missed opportunity to demonstrate seniority.

What usually comes next

Expect questions that build on this concept. For example: "How would you modify this to track MRR changes month-over-month, categorizing revenue as new, churn, expansion, or contraction?" or "This query runs on a 500 million row table and is slow. How would you optimize it?" (Answer: indexing, or preferably, creating a monthly summary table in a data model). Another likely question is, "How would you handle prorated amounts or discounts?"

A concrete example

To calculate MRR for October 2024, a good query would look like this. Note the use of 12.0 to prevent integer division. The logic correctly identifies all subscriptions that were active for any part of the month.

Select

SUM(CASE WHEN plan_type = 'annual' THEN monthly_price / 12.0 ELSE monthly_price END) AS mrr FROM subscriptions WHERE start_date <= '2024-10-31'

AND (end_date > '2024-10-01' OR end_date IS NULL);