Scenario: Helix Systems' finance team is tracking cumulative salary expenditure commitments over time.
Task: Write a query to return each calendar month in which at least one salary became effective, the total salary amount that became effective in that month, and the running total of all salary commitments from the earliest such month through that month.
Assumptions:
- A salary month is identified by its first day.
- A month's
monthly_salary_totalis the combinedamountacross salaries that became effective in that month. - A month's
cumulative_salaryis the combinedmonthly_salary_totalfrom the earliest salary month through that month inclusive. - The result covers only months containing at least one salary that became effective.
Output:
- One row per qualifying salary month.
- Columns in this order:
salary_month,monthly_salary_total,cumulative_salary. - Sorted by
salary_monthascending.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
DATE_TRUNC('month', effective_date) AS salary_month,
SUM(amount) AS monthly_salary_total,
SUM(SUM(amount)) OVER (
ORDER BY
DATE_TRUNC('month', effective_date) ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_salary
FROM
salaries
GROUP BY
DATE_TRUNC('month', effective_date)
ORDER BY
salary_monthThe shape
Salaries are grouped by their effective month and accumulated forward. DATE_TRUNC('month', effective_date) puts every salary into its effective-month bucket, SUM(amount) collapses each month into a single committed-salary figure, and the windowed SUM(SUM(amount)) runs that monthly figure through to a running total starting at the earliest salary month.
Clause by clause
SELECT DATE_TRUNC('month', effective_date) AS salary_month, SUM(amount) AS monthly_salary_totalproduces one row per salary month with the combined salary amounts that became effective in that month. Salaries effective on any day in January 2018 land in the2018-01-01bucket.SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', effective_date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salaryaccumulates the monthly totals in month order. The innerSUMis the per-month aggregate; the outer windowedSUMadds those monthly figures together from the earliest month through the current one.FROM salaries GROUP BY DATE_TRUNC('month', effective_date)aggregates the raw rows so each row entering the window function is already one month.ORDER BY salary_monthsorts the final output chronologically.
Why the running total only covers months with salaries
There is no row for a calendar month with no salaries becoming effective, so the running total skips that month. The cumulative figure jumps from the prior salary month to the next salary month. That matches the prompt: the result covers only months with at least one effective salary. If the requirement were a continuous monthly series including idle months at zero, a date spine joined to this aggregate would be needed before the accumulation.
You practiced building a cumulative commitment series from per-month totals, with each month carrying both its own commitments and the all-time total through that point.
