Amazon-style Company ChallengeEasyVerified answerSQLite live

Monthly Order Revenue Trend

What was the total order count and revenue for each month in 2024?

  • Aggregation
  • Date analysis
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

Finance Team is building a monthly revenue dashboard and needs order volume and revenue totals for each month.

Show monthly order count and revenue totals for the orders table.

Return

  • order_month (formatted YYYY-MM)
  • total_orders
  • monthly_revenue (rounded to 2 decimals)

Constraints

  • Return calendar-month labels in YYYY-MM format
  • Count orders and sum revenue within each calendar month
  • Order by order_month ascending

Data you will use

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

orders

  • order_idINTEGER
  • order_dateDATETIME
  • total_amountREAL

Hints, when you need them

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

Hint 1

All the data you need is in the orders table — no JOIN required. You need to group rows by month. SQLite's strftime function can extract a YYYY-MM month string from a datetime column.

Hint 2

Use strftime('%Y-%m', order_date) AS order_month in both SELECT and GROUP BY. Then COUNT(order_id) and ROUND(SUM(total_amount), 2). Order by order_month.

Hint 3

Scaffold: derive a YYYY-MM month key, group orders by that same expression, calculate count and revenue, and sort by the month key.

Verified SQL answer

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

Reveal solution and explanation
SELECT strftime('%Y-%m', order_date) AS order_month, COUNT(order_id) AS total_orders, ROUND(SUM(total_amount), 2) AS monthly_revenue FROM orders GROUP BY strftime('%Y-%m', order_date) ORDER BY order_month;

Why this works

strftime('%Y-%m', order_date) converts a full datetime like 2024-01-15 10:30:00 into 2024-01. Grouping by this expression buckets all January orders together and all February orders together. No JOIN needed — all columns are in orders.

Success check

2 months — January (22.92, 5 orders) and February (09.92, 5 orders)

Expected result

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

order_monthtotal_ordersmonthly_revenue
2024-015422.92
2024-025509.92

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.