Calculate Monthly Recurring Revenue (MRR) with SQL
This tests your ability to translate a business metric into a robust SQL query, handling time-series logic. A good answer filters for active subscriptions, sums the price, and correctly amortizes annual plans. A red flag is using incorrect date filtering.
What's really being asked
This question tests your ability to translate a core business metric into a precise SQL query. It's not about a simple SUM(), but about correctly defining "active" within a specific time window. The follow-up about annual plans tests your understanding of revenue amortization and codifying business rules. A senior candidate is expected to go beyond the ad-hoc query and discuss the trade-offs and architectural best practices, such as building a dedicated data model for recurring analysis.
The full answer
First, writing a base query that sums the monthly_price. Second, adding a robust WHERE clause to filter for all subscriptions active during the current month. This means the subscription started on or before the last day of the month and ended on or after the first day of themonth (or hasn't ended). Third, handling the annual plan by using a CASE statement to divide the annual price by 12, effectively amortizing it. Fourth, proactively mentioning that for regular reporting, this ad-hoc query is fragile and inefficient. The best practice is to build an intermediate monthly summary table (a fact model) that pre-calculates MRR for each customer each month, simplifying all downstream analysis.
The mistakes people make
One major red flag is incorrect date logic. A query like WHERE start_date >= '2024-07-01' AND end_date <= '2024-07-31' is wrong because it misses subscriptions that started before July and continue through it. Another mistake is ignoring the annual plan distinction, which would dramatically overstate MRR if the annual price is stored in the monthly_price column. Finally, a junior-level answer just gives the query and stops. A senior answer must discuss the limitations of the ad-hoc approach and propose a more scalable data modeling solution.
What usually comes next
Expect questions like: "How would you calculate MRR changes like new, churn, expansion, and contraction?" which requires comparing this month to last month. Or, "This query is getting slow on our 100 million row table. How would you speed it up?" which is a direct prompt to discuss aggregation strategies and data modeling. They might also ask how your query handles a customer with multiple simultaneous subscriptions to test your understanding of aggregations.
A concrete example
To calculate MRR for the current month, including amortized annual plans, the core logic would be:
Select
SUM(CASE
WHEN plan_type = 'annual' THEN monthly_price / 12.0
ELSE monthly_price
END) AS mrr
FROM subscriptions
WHERE
start_date <= DATE_TRUNC('month', NOW()) + INTERVAL '1 month - 1 day' -- Started on or before end of current month
AND (end_date >= DATE_TRUNC('month', NOW()) OR end_date IS NULL); -- Ends on or after start of current month, or is ongoing
Using 12.0 ensures floating-point division. The date functions may vary by SQL dialect (e.g., EOMONTH in SQL Server).
Interview question
To calculate MRR for July 2024, which `WHERE` clause correctly identifies all subscriptions active at any point during that month?
- a.WHERE start_date <= '2024-07-31' AND (end_date >= '2024-07-01' OR end_date IS NULL)Correct
- b.WHERE start_date >= '2024-07-01' AND end_date <= '2024-07-31'
- c.WHERE start_date <= '2024-07-01' AND (end_date >= '2024-07-31' OR end_date IS NULL)
- d.WHERE DATE_TRUNC('month', start_date) = '2024-07-01'
Why? this is the answer
The correct logic finds subscriptions that started on or before the end of the month and ended on or after the start of the month. A common mistake is to only select subscriptions that both start and end within the month, which misses longer-running active subscriptions.
Just read this? Test yourself on what you have been reading.
Read the original → getdbt.com
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on sql — each one lists the topics its interview covers.
See open roles