Calculate MRR with SQL including annual plans

WHAT THIS TESTS The interviewer checks two things: whether you know MRR is a normalized monthly figure, and whether you can write the date-overlap logic that identifies active subscriptions for the period.

A GOOD ANSWER COVERS MRR is the sum of normalized monthly revenue from all subscriptions active during the month. A subscription is active in the current month if it started on or before the end of the month and has not ended before the month began, that is end_date is null or end_date is on or after the first of the month. The query sums monthly_price under those conditions. State the active-window predicate explicitly because off-by-one date logic is the most common mistake.

A query reads roughly: SELECT SUM(monthly_price) FROM subscriptions WHERE start_date is less than or equal to the last day of the month AND (end_date IS NULL OR end_date is greater than or equal to the first day of the month).

For annual plans flagged by plan_type, you must normalize so a yearly payment is spread evenly. Use SUM(CASE WHEN plan_type = annual THEN annual_price divided by 12 ELSE monthly_price END). The key idea is that MRR smooths revenue: an annual plan contributes one twelfth each month, not its full value in the signup month.

COMMON WRONG ANSWERS Booking the entire annual charge in a single month, which spikes MRR and breaks comparability. Forgetting the overlap predicate so cancelled or future subscriptions leak in. Counting a subscription whose end_date fell earlier this period.

LIKELY FOLLOW-UPS How would you compute MRR movements: new, expansion, contraction, and churned MRR? How do mid-month upgrades or proration affect the number? How would you build a monthly MRR trend across many months with a date spine?

ONE CONCRETE EXAMPLE A user on a 1200 per year annual plan contributes 100 to MRR each month, not 1200 in January. Combined with a second user paying 30 per month, the company MRR for the month is 130, giving a stable, comparable monthly figure.