skip to content

questions

18

In SQL, why does GROUP BY EXTRACT(MONTH FROM order_date) merge different years, and how do you bucket orders by calendar month?

level: juniorimportance: must knowfreq 56%

answer

  1. what one extracted field remembers
  2. March of two different years
  3. group by two fields
  4. truncate to the month's first day
  5. no single truncation spelling

basics

~20 s

EXTRACT returns a single field as a number, so March 2023 and March 2024 both give 3 and share a group. Group by year and month together, or by each date truncated to its month's first day.

solid answer

~40 s

`EXTRACT(MONTH FROM order_date)` returns only the month number, 1 to 12, with no year attached. Three years of orders therefore collapse into twelve month groups at most, and March 2023 adds silently to March 2024. The output looks plausible, so the bug tends to surface only when someone compares totals with another source. One fix is to group by two fields, `EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)`, which works on engines that have `EXTRACT`, such as PostgreSQL, MySQL and Oracle. The other is to truncate each date to its month's first day and group by that single value, which sorts chronologically and makes a ready chart axis. Truncation has no single portable spelling: PostgreSQL has `date_trunc('month', order_date)`, Oracle has `TRUNC(order_date, 'MM')`, MySQL can use `DATE_FORMAT(order_date, '%Y-%m-01')`, and SQLite has `date(order_date, 'start of month')`.

code

sql · 14 lines
sql
-- two fields: works wherever EXTRACT exists
SELECT EXTRACT(YEAR FROM order_date) AS yr,
       EXTRACT(MONTH FROM order_date) AS mon,
       SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY yr, mon;

-- one key: date_trunc is PostgreSQL's spelling of truncation
SELECT date_trunc('month', order_date)::date AS month_start,
       SUM(amount) AS revenue
FROM orders
GROUP BY date_trunc('month', order_date)::date
ORDER BY month_start;

go deeper

for a junior

Recall that EXTRACT returns a single number with no year attached, and write the two-field GROUP BY from memory. Then say that truncating to the month's first day gives one sortable key.

for a middle

Explain which engines have EXTRACT and how truncation is spelled on each. Mention that weekday numbering differs by engine, and that year plus ISO week disagree around New Year on PostgreSQL.

for a senior

Ask whether the reader wants a timeline or seasonality before fixing the query, and check month buckets against a known total. A merged-year bug looks plausible, so it survives review unless someone reconciles the numbers.

for a principal

Weigh a shared calendar table against per-engine truncation. The table costs a little upkeep, yet it gives every report the same weeks, fiscal periods and empty months, which is what stops teams publishing conflicting numbers.

## What EXTRACT returns `EXTRACT(field FROM source)` reads **one field** out of a date or timestamp and returns it as a number. `EXTRACT(MONTH FROM DATE '2024-03-15')` returns 3, and `EXTRACT(YEAR FROM DATE '2024-03-15')` returns 2024. The form is widely available but not universal: PostgreSQL, MySQL and Oracle have it, SQLite does not, and SQL Server uses its own `DATEPART`, which is not portable. Three details decide how it behaves in a real query: - **The result knows nothing else.** The number 3 carries no year, so nothing tells March 2023 from March 2024. - **The field must fit the type.** On PostgreSQL, fields smaller than a day cannot be taken from a `date`, so `EXTRACT(HOUR FROM DATE '2024-03-15')` is an error. - **NULL stays NULL.** A row with no `order_date` gives a NULL field, and `GROUP BY` puts those rows in one extra group. ## Why a month-only group merges years This query runs without complaint and returns a number for every month: ```sql SELECT EXTRACT(MONTH FROM order_date) AS month_no, SUM(amount) AS revenue FROM orders GROUP BY EXTRACT(MONTH FROM order_date); ``` With three years of non-null dates it returns at most 12 rows, and each row adds up the same month from all three years. That is the right query for a **seasonality** report, where you want every March together. It is the wrong query for a **timeline**, where each March is a separate point. The mistake is hard to see because every row looks like a sensible total, so ask which of the two the reader expects before you write the `GROUP BY`. ## Two correct ways to bucket by month 1. **Group by two fields.** `GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)` keeps the years apart on any engine with `EXTRACT`. You sort by both fields, and you build a label yourself if a chart needs one. 2. **Truncate to the period's start.** Replace each date with the first day of its month, then group by that one value. It sorts chronologically, it is a real date for further arithmetic, and it plots directly on a time axis. A third, fully portable option is a **calendar table** with one row per day and a `month_start` column. You join to it and group by that column, and it also gives you the months that had no orders. ## Truncation has no single spelling | Engine | First day of the month | Result | |---|---|---| | PostgreSQL | `date_trunc('month', order_date)` | a timestamp; cast it with `::date` when you want a date | | Oracle | `TRUNC(order_date, 'MM')` | a `DATE` | | MySQL | `DATE_FORMAT(order_date, '%Y-%m-01')` | a string in sortable ISO form | | SQLite | `date(order_date, 'start of month')` | text in ISO form | Avoid a label such as `'Mar 2024'` as the bucket: it sorts alphabetically, so April comes before March. PostgreSQL's `date_trunc` also deserves one warning. Given a `date`, it returns a `timestamp with time zone` in the session's zone, so the cast back to `date` keeps a session setting out of what you print. ## Weekday numbers are not portable Extracting a weekday is where `EXTRACT` stops being a portable habit, because the numbering belongs to each engine: - **PostgreSQL** has `EXTRACT(DOW ...)`, Sunday 0 to Saturday 6, and `EXTRACT(ISODOW ...)`, Monday 1 to Sunday 7. - **MySQL** has no weekday field in `EXTRACT`. `DAYOFWEEK()` runs Sunday 1 to Saturday 7, and `WEEKDAY()` runs Monday 0 to Sunday 6. - **Oracle** has no weekday field in `EXTRACT` either, and `TO_CHAR(d, 'D')` depends on the session's territory setting. - **SQLite** has `strftime('%w', d)`, Sunday 0 to Saturday 6, returned as text. A query that filters on day 1 therefore means Monday on PostgreSQL and Sunday on MySQL's `DAYOFWEEK()`. State which numbering a query relies on, or keep a weekday column in a calendar table so nobody has to remember. ## The same trap with weeks Grouping by `EXTRACT(YEAR ...)` and `EXTRACT(WEEK ...)` has a related flaw on PostgreSQL. `WEEK` is the ISO 8601 week, while `YEAR` is the calendar year, and the two disagree around New Year. 1 January 2021 has year 2021 and week 53, because it belongs to the last ISO week of 2020, so one real week splits across two groups. Pair `WEEK` with `ISOYEAR` instead, or truncate to the start of the week. ## Filtering to one month Use a date range rather than comparing extracted fields: `order_date >= DATE '2024-03-01' AND order_date < DATE '2024-04-01'`. It reads the same on any engine that accepts the literal, and it leaves the column bare, which matters for index use.

  • How do you keep a weekday filter correct when a query moves between engines?
    Do not carry the raw number across, because Sunday is 0 under PostgreSQL's `DOW` and 1 under MySQL's `DAYOFWEEK()`. Rewrite the filter for the target engine's numbering, ideally the Monday-first `ISODOW` on PostgreSQL, or join a calendar table with its own weekday column, which reads the same everywhere.
  • Why can grouping by EXTRACT(YEAR ...) and EXTRACT(WEEK ...) split a week on PostgreSQL?
    `WEEK` follows ISO 8601 while `YEAR` is the calendar year. 1 January 2021 reports year 2021 and week 53, though it sits in the same ISO week as the end of December 2020, so that week lands in two groups. Use `ISOYEAR` with `WEEK`, or truncate to the week's start.
  • When is a month-only GROUP BY the right query?
    When the question is seasonality: does March usually sell better than June, across all years? There you want every March together, and `EXTRACT(MONTH ...)` alone says that exactly. Label the column so a reader does not take it for a timeline, and check that each month contributes the same number of years.

  • EXTRACT(MONTH ...) keeps the year internally, so March 2023 and March 2024 stay separate groups.
  • date_trunc is portable SQL that runs unchanged on every engine.
  • Weekday numbers are the same on every engine, so filtering on day 1 is portable.
  • A month label such as 'Mar 2024' sorts chronologically in ORDER BY.
  • Pairing EXTRACT(YEAR ...) with EXTRACT(WEEK ...) always keeps one calendar week in one group.
open as a page

In SQL, what do ROUND, TRUNC, CEIL and FLOOR each return for 2.7 and for -2.7, and why?

level: juniorimportance: must knowfreq 55%

basics

~10 s

ROUND goes to the nearest integer (3, -3), TRUNC drops the fraction toward zero (2, -2), CEIL moves up toward positive infinity (3, -2), and FLOOR moves down toward negative infinity (2, -3).

open as a page

In SQL, how do you extract the text before the first space in full_name with SUBSTRING and POSITION, and what breaks for a one-word name?

level: juniorimportance: must knowfreq 52%

basics

~20 s

SUBSTRING(full_name FROM 1 FOR POSITION(' ' IN full_name) - 1) returns the text before the first space. A one-word name makes POSITION return 0, so the length is -1, which some engines reject and others turn into an empty result.

open as a page

In SQL, what does created_at AT TIME ZONE 'Europe/Berlin' do, and how does the answer depend on whether created_at has a time zone?

level: middleimportance: must knowfreq 50%

basics

~10 s

On PostgreSQL, AT TIME ZONE converts by input type: a timestamp with time zone becomes Berlin's wall-clock reading, and a plain timestamp is read as Berlin's wall clock and becomes an instant.

open as a page

In SQL, what do SELECT 7 / 2 and SELECT -7 / 2 return on an integer-dividing engine, and which common engines differ?

level: middleimportance: must knowfreq 60%

basics

~20 s

An integer-dividing engine returns 3 and -3, because the quotient truncates toward zero. PostgreSQL, SQL Server and SQLite divide integers that way; MySQL and Oracle return a fractional result, 3.5 and -3.5, from the same expression.

open as a page

In SQL, why can COUNT(first_name || ' ' || middle_name) come in below the customer row count, and does CONCAT fix that on every engine?

level: middleimportance: must knowfreq 58%

basics

~20 s

With standard ||, one NULL operand makes the whole expression NULL, and COUNT skips NULL, so rows with no middle name drop out. CONCAT is no universal fix: PostgreSQL and SQL Server ignore NULLs, but MySQL's CONCAT returns NULL.

open as a page

In SQL, what does TRIM(LEADING '0' FROM code) return for '00120' and '000', and what does a bare TRIM(code) remove?

level: juniorimportance: should knowfreq 42%

basics

~20 s

TRIM(LEADING '0' FROM code) turns '00120' into '120' and '000' into an empty string, or NULL on Oracle. A bare TRIM(code) removes only space characters from both ends, leaving tabs, newlines and interior spaces in place.

open as a page

In SQL, how do CURRENT_DATE and CURRENT_TIMESTAMP differ, and why does PostgreSQL's CURRENT_TIMESTAMP stay frozen through a long transaction?

level: middleimportance: should knowfreq 42%

basics

~10 s

CURRENT_DATE gives today's date and CURRENT_TIMESTAMP adds the time of day. PostgreSQL fixes both at the transaction's start, so they do not advance inside a transaction; clock_timestamp() reads the real clock.

open as a page

In SQL, what does DATE '2024-01-31' + INTERVAL '1' MONTH return, and why does a monthly schedule built by repeated addition drift?

level: middleimportance: should knowfreq 46%

basics

~20 s

PostgreSQL and MySQL clamp 31 January plus a month to 29 February 2024, and the lost day never returns: a second month gives 29 March. Compute each date from the original anchor, not the previous result.

open as a page

In SQL, why can ROUND(2.5) return 3 on a DECIMAL value but 2 on a FLOAT value?

level: middleimportance: should knowfreq 42%

basics

~20 s

Exact types such as DECIMAL commonly break a tie away from zero, giving 3, while approximate types often round ties to the nearest even number, giving 2. The float rule varies by engine and platform, so it is not portable.

open as a page

In SQL, what does MOD(-7, 3) return, and why does WHERE MOD(x, 2) = 1 miss some odd numbers?

level: middleimportance: should knowfreq 38%

basics

~20 s

MOD(-7, 3) returns -1 because the remainder takes the sign of the dividend on the common engines. A negative odd x gives MOD(x, 2) = -1, so the test misses it; write MOD(x, 2) <> 0 instead.

open as a page

In SQL, why do CHAR_LENGTH and OCTET_LENGTH give different results for a customer name like 'José', and when do you use each?

level: middleimportance: should knowfreq 45%

basics

~20 s

CHAR_LENGTH counts characters and OCTET_LENGTH counts bytes. In UTF-8 the é in 'José' takes two bytes, so the results are 4 and 5. Use characters for rules people reason about, and bytes for storage or payload limits.

open as a page

A daily sales report for users in several time zones shows a short last day each month, and its buckets shift by an hour twice a year; how do you diagnose and fix it in SQL?

level: seniorimportance: should knowfreq 48%

basics

~20 s

A short last day and shifting buckets are usually two bugs. An inclusive or UTC upper bound cuts the day, so use a half-open range in the report's zone. Fixed offsets shift buckets, so use a named zone.

open as a page

A revenue report's totals miss finance's by a few cents and its conversion-rate column is always 0; how do you diagnose and fix both in SQL?

level: seniorimportance: should knowfreq 46%

basics

~20 s

The always-0 column is integer division: cast one operand to DECIMAL before dividing and guard the denominator with NULLIF. The cents usually come from rounding: compare your rounding stage, tie rule and any truncation with finance's on one mismatched document.

open as a page

After moving a customer import to another SQL engine, SELECT DISTINCT name returns fewer rows and ORDER BY changes; how does collation explain both, and how do you pin the behaviour?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A collation defines which strings count as equal and how they sort. A case- or accent-insensitive default merges 'Ann' and 'ANN' under DISTINCT, and a different ruleset reorders ORDER BY. Declare the collation on the column or with COLLATE.

open as a page

For a reporting system with users in several time zones, which zone should store event times, which should define a day's bucket, and what does each choice cost?

level: principalimportance: should knowfreq 37%

basics

~20 s

Store past events as instants, usually in UTC, and pick bucket zones by who uses the number. One business zone keeps totals reconcilable; viewer zones match local expectations but stop totals agreeing; serving both needs finer rollups.

open as a page

As a lead on a customer data team, how do you decide whether SQL string cleaning belongs in each query, a generated column or the ingest pipeline?

level: principalimportance: should knowfreq 26%

basics

~20 s

Decide by how many consumers need one definition and whether the step loses data. Keep lossy, contextual cleaning in the pipeline with the raw value retained, deterministic row-level forms in generated columns, and display formatting in queries.

open as a page

In SQL, what precision and scale does DECIMAL arithmetic return, and why does 1.0 / 3 show different digits on each engine?

level: middleimportance: nice to knowfreq 30%

basics

~20 s

Addition keeps the larger operand scale and multiplication adds the scales on most engines. Division is often inexact, so each engine picks its own result scale, and 1.0 / 3 can show 20, 5 or 6 decimals.

open as a page