Date Operations & Time-Based Analytics SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Fiscal Quarter Segmentation

Aggregate revenue by fiscal quarter for fiscal year 2024, where April–June is Q1.

  • Aggregation
  • CASE expressions
  • Date analysis
  • Type conversion
  • Filtering

Exercise brief

Understand the request

Financial planning analyst Planning reports use an April-to-March fiscal year instead of calendar quarters.

Planning reports use an April-to-March fiscal year instead of calendar quarters. Aggregate revenue by fiscal quarter for fiscal year 2024, where April–June is Q1.

Return

  • Return fiscal_quarter, fiscal_year, total_sales in this exact left-to-right order.

Constraints

  • Map January–March to Q4 of the preceding fiscal year.
  • Filter using the derived fiscal year.
  • Order by fiscal year and fiscal quarter.

Data you will use

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

orders

  • order_idINTEGER
  • order_dateDATE
  • order_totalDECIMAL

Hints, when you need them

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

Hint 1

A fiscal year starting in April shifts calendar months: Apr–Jun is fiscal Q1.

Hint 2

Jan–Mar belong to the PREVIOUS fiscal year, so subtract 1 from the calendar year there.

Hint 3

Compute fiscal_year in the WHERE the same way you compute it in SELECT.

Verified SQL answer

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

Reveal solution and explanation
SELECT CASE WHEN CAST(strftime('%m', order_date) AS INTEGER) BETWEEN 4 AND 6 THEN 'Q1' WHEN CAST(strftime('%m', order_date) AS INTEGER) BETWEEN 7 AND 9 THEN 'Q2' WHEN CAST(strftime('%m', order_date) AS INTEGER) BETWEEN 10 AND 12 THEN 'Q3' ELSE 'Q4' END AS fiscal_quarter, CASE WHEN CAST(strftime('%m', order_date) AS INTEGER) < 4 THEN CAST(strftime('%Y', order_date) AS INTEGER) - 1 ELSE CAST(strftime('%Y', order_date) AS INTEGER) END AS fiscal_year, SUM(order_total) AS total_sales FROM orders WHERE CASE WHEN CAST(strftime('%m', order_date) AS INTEGER) < 4 THEN CAST(strftime('%Y', order_date) AS INTEGER) - 1 ELSE CAST(strftime('%Y', order_date) AS INTEGER) END = 2024 GROUP BY fiscal_quarter, fiscal_year ORDER BY fiscal_year, fiscal_quarter;

Why this works

Fiscal calendars are everywhere in finance and rarely align to Jan–Dec. The trick is two CASE expressions: one maps month→fiscal quarter, the other rolls Jan–Mar back into the prior fiscal year. The math is pure arithmetic, so it ports cleanly; only the month extraction changes per engine.

Success check

Every populated fiscal 2024 quarter appears once with the correct revenue.

Expected result

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

fiscal_quarterfiscal_yeartotal_sales
Q120241840
Q220241050
Q32024960

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.