Drill 7 of 12 in General questions
0

Interview Question Bank · Pivot with SUM FILTER · COALESCE

7. Q1 revenue, one column per month

MediumGeneralQuestion #199

Concept: Pivot with SUM FILTER · COALESCEshow

To turn months into columns, group by the row label only and write one aggregate per column, each filtered to its month: SUM(x) FILTER (WHERE EXTRACT(MONTH FROM d) = 1). Filter the date range in WHERE first, because a month number alone matches every year. A month with no rows sums to NULL, so wrap each column in COALESCE(..., 0) when the report wants zeros.

Example (different data):

SELECT warehouse,
  COALESCE(SUM(units) FILTER (WHERE EXTRACT(DOW FROM shipped_on) = 1), 0) AS monday,
  COALESCE(SUM(units) FILTER (WHERE EXTRACT(DOW FROM shipped_on) = 2), 0) AS tuesday
FROM shipments
GROUP BY warehouse;

Your task

Brightwater Outfitters' board pack has a table of first-quarter 2025 revenue with one row per region and one column per month. The sales table also holds sales from late 2024 and from April 2025.

For January to March 2025, return region, jan, feb, mar (revenue summed for each month, 0 when there were no sales) and q1_total. Sort by q1_total descending, then region.

Starting Postgres in your browser…

Checking your access…