SQLMaxx
Sign in Start free
N033-H2 Tier 3 · Intermediate · hard ecommerce · Brightlane

Return the month component of `'2024-03-15'` and the month component of `'2023-03-15'` in a single row

Part of Date Truncation and Extraction in SQL

The problem

A Brightlane data analyst is investigating why a year-over-year report is collapsing records from March 2024 and March 2023 into a single group. The grouping logic relies on the month component pulled out of the event date.

Write a query to return the month component of '2024-03-15' and the month component of '2023-03-15' in a single row.

Output:

  • A single row with columns month_2024 and month_2023, each containing the month number from the corresponding date.
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(
    MONTH
    FROM
      '2024-03-15'::date
  ) AS month_2024,
  EXTRACT(
    MONTH
    FROM
      '2023-03-15'::date
  ) AS month_2023

The shape

EXTRACT(month FROM ...) returns a number between 1 and 12 with no year component attached, so two dates that share a month but belong to different years return the same number. The March 15 dates from 2024 and 2023 both return 3. The analyst's year-over-year report keys on this value, which is why the two periods collapse into a single group.

Clause by clause

  • SELECT EXTRACT(month FROM '2024-03-15'::date) AS month_2024 returns the month component of the 2024 date as the number 3. The ::date cast resolves the string as a calendar date, and EXTRACT reads the month off it.
  • EXTRACT(month FROM '2023-03-15'::date) AS month_2023 does the same against the 2023 date. The cast and the extraction are identical; only the year inside the literal differs.
  • There is no FROM because both expressions evaluate against literals directly. Putting them side by side in the same row makes the equality visible at a glance: the two columns both show 3.

Why EXTRACT(month ...) produces the same value across years

EXTRACT is a decomposition tool. Its job is pulling one named component out of a datetime, and the month component of any March date is 3 regardless of which year the date belongs to. The year is a separate component, accessible through EXTRACT(year FROM ...). A report that needs both pieces has to extract them both and key on the pair, or, more commonly, use DATE_TRUNC('month', ...) instead. DATE_TRUNC('month', ...) returns a full timestamp like '2024-03-01 00:00:00' that includes the year inside the boundary value, which keeps March 2024 and March 2023 in separate groups.

The trap

The collapse is silent. Both dates extract to 3, both rows survive every downstream lookup that keys on the month number, and the year-over-year report shows what looks like a doubled total for March with no warning. The analyst's instinct, when seeing two periods merge, is to look for a join or a filter that's misbehaving; the actual cause is the extraction discarding the year on every row at the start of the pipeline. The rule is general: EXTRACT(month ...) is the right tool when the question is which month of the year a single datetime falls in, and the wrong tool whenever the question spans more than one year.

You practiced EXTRACT(month FROM ...) returning the same month number across years — the recurring source of a year-over-year report that collapses two calendar periods into one bucket.

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.