← All tasksSQL · Senior

Month-over-month revenue growth

Compute monthly revenue and growth versus the previous month: columns month (the first day of the month, type date), revenue and growth_pct, the percentage change rounded to one decimal (NULL for the first month). Order by month.

For example, 200 → 300 is 50.0, and 300 → 150 is -50.0.

Tables

CREATE TABLE orders (
  id         int PRIMARY KEY,
  created_at date NOT NULL,
  total      numeric(10, 2) NOT NULL
);

SELECT date_trunc('month', created_at)::date AS month,
       sum(total) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO orders VALUES
  (1, '2024-01-05', 100.00), (2, '2024-01-20', 100.00),
  (3, '2024-02-11', 300.00),
  (4, '2024-03-02', 150.00);

-- growth rounded to one decimal place
INSERT INTO orders VALUES
  (1, '2024-04-01', 300.00),
  (2, '2024-05-15', 400.00),
  (3, '2024-06-30', 401.00);

-- a single month: growth is NULL
INSERT INTO orders VALUES (1, '2024-09-09', 50.00);