A retailer's event table stores a TIMESTAMP for every hour of activity, and the finance team wants monthly totals. Which approach is the most efficient way to group the rows?
Show answer and explanation
Answer: A
TIMESTAMP_TRUNC with the MONTH part maps every hourly value to the first instant of its month in one function call, and grouping on that value yields monthly totals. Note that DATE_TRUNC accepts a date, not a timestamp, so TIMESTAMP_TRUNC is the right function for this column. String concatenation is clumsier and sorts poorly, a backfilled column adds storage and maintenance for no benefit, and spreadsheets do not scale to large tables.
Why the other options are wrong
- B. Building year-month strings yields a text value that sorts lexically and loses date semantics, so it is clumsier than a typed truncated timestamp for analysis.
- C. A backfilled month column duplicates information already in the timestamp, costs storage, and must be maintained for every future row, which is unnecessary here.
- D. Spreadsheets cannot process a large event table in one pass, and the work is manual and not repeatable, so it fails the production requirement.