Airbnb-style Company ChallengeHardVerified answerSQLite live

Monthly Booking Trend

Return month (YYYY-MM), bookings (count that month), total_nights (sum of nights that month), and cumulative_bookings (running total). Order by month ascending.

  • Window functions
  • Subqueries
  • Aggregation
  • Date analysis
  • Sorting

Challenge brief

Understand the request

Growth — Booking Momentum The growth team monitors monthly booking volume and cumulative totals to track platform momentum and forecast seasonal peaks.

Show bookings and total nights per month with a running cumulative booking count.

Return

  • month
  • bookings
  • total_nights
  • cumulative_bookings

Constraints

  • Group bookings by calendar month in YYYY-MM form
  • Count bookings and sum nights per month
  • Use an explicit row-based frame for the running booking total
  • Order by month

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

bookings

  • booking_dateDATE
  • nightsINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Reduce booking facts to one row per calendar month first.

Hint 2

Apply the cumulative calculation to monthly booking counts.

Hint 3

Make the window frame explicitly row-based and preserve chronological order.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH monthly AS (SELECT strftime('%Y-%m', booking_date) AS month, COUNT(*) AS bookings, SUM(nights) AS total_nights FROM bookings GROUP BY month), running AS (SELECT month, bookings, total_nights, SUM(bookings) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_bookings FROM monthly) SELECT month, bookings, total_nights, cumulative_bookings FROM running ORDER BY month

Why this works

The first CTE groups bookings by calendar month using strftime. The second adds a running SUM window function over those monthly counts — giving cumulative booking volume. This is the standard growth-tracking pattern combining GROUP BY with a window function.

Success check

Returns one chronological row per booking month with a deterministic cumulative total

Expected result

Use this output to verify values, aliases, ordering, and row count.

monthbookingstotal_nightscumulative_bookings
2023-08102910
2023-091411

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.