SQLMaxx
Sign in Start free
N057-H3 Tier 4 · Advanced · hard ecommerce · Brightlane

Return the year, the quarter number from 1 through 4, the number of `orders` placed in that year-quarter, and the total `orders` revenue for that year-quarter

Part of Grouping by Date Periods in SQL

The problem

Scenario: Brightlane's CFO wants a quarterly revenue report where year and quarter are reported as two separate numeric columns rather than a single date, so the values are unambiguous on the report.

Task: Write a query to return the year, the quarter number from 1 through 4, the number of orders placed in that year-quarter, and the total orders revenue for that year-quarter.

Assumptions:

  • The year value is the four-digit calendar year of the order.
  • The quarter value is the calendar quarter of the order, expressed as a number from 1 (January–March) through 4 (October–December).
  • One result row covers every order whose calendar year and calendar quarter match.

Output:

  • One row per (year, quarter) combination present in the data.
  • Columns in this order: year, quarter, order_count, revenue.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  EXTRACT(
    YEAR
    FROM
      ordered_at
  ) AS YEAR,
  EXTRACT(
    quarter
    FROM
      ordered_at
  ) AS quarter,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM
  orders
GROUP BY
  EXTRACT(
    YEAR
    FROM
      ordered_at
  ),
  EXTRACT(
    quarter
    FROM
      ordered_at
  )

The shape

EXTRACT pulls the year and the quarter number out of ordered_at as two independent numeric values, and the grouping happens on the pair. Each row reports the time dimension as two unambiguous columns of numbers, not as a single truncated quarter-start date, which is the shape the CFO's report needs.

Clause by clause

  • SELECT EXTRACT(year FROM ordered_at) AS year, EXTRACT(quarter FROM ordered_at) AS quarter, COUNT(*) AS order_count, SUM(total_amount) AS revenue returns one row per (year, quarter) combination. Each EXTRACT returns a number, so the output shows 2024 and 3 instead of a July 1 date. COUNT(*) and SUM(total_amount) aggregate the orders that fall inside the pair.
  • FROM orders reads every placed order.
  • GROUP BY EXTRACT(year FROM ordered_at), EXTRACT(quarter FROM ordered_at) uses the same two expressions as the grouping keys. The grouping is on the pair: Q1 2024 and Q1 2025 are kept apart by the year value even though they share quarter = 1.

Why EXTRACT for both columns

date_trunc('quarter', ordered_at) would return one timestamp per quarter — for example, 2024-07-01 for Q3 2024 — which is correct for charting but ambiguous on a printed report. A reader sees July 1 and has to mentally translate it back to "Q3." Pulling the year and the quarter number out as two separate columns removes that translation step. The two functions both live in this node for exactly this reason: date_trunc keeps the calendar continuous, and EXTRACT decomposes it into parts that a human can read at a glance.

The trap

The year and quarter columns are both numeric, and a reader who tries to combine them as if they were a single date — for example, by computing year * 10 + quarter to sort the result — will get a number that sorts correctly but means nothing as a date. The two columns are independent calendar parts. They sort lexicographically in pairs: (2022, 1), (2022, 2), ... (2022, 4), (2023, 1), ... which is the natural calendar order, but only if both columns are sorted together. Treating either as a standalone scalar that encodes both dimensions throws away information the report deliberately separated.

You practiced extracting year and quarter as independent numeric values so the time dimension is reported as two columns rather than a single truncated date.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.