Skip to content
Onsite

Month-over-month revenue growth

MediumP2Coding

Table orders:

ColumnType
idint (primary key)
amountint
created_attimestamp

For each month that has orders, return the revenue and its growth in percent compared with the previous calendar month:

growth_pct = (revenue - previous_revenue) / previous_revenue * 100, rounded to 2 decimals.

growth_pct is NULL when the previous calendar month has no orders, or when its revenue is 0.

Output columns: month (text YYYY-MM), revenue, growth_pct. Order by month.

Schema

CREATE TABLE orders (
  id int PRIMARY KEY,
  amount int NOT NULL,
  created_at timestamp NOT NULL
);

Example 1

Input
revenue: Jan 100, Feb 150, Mar 120
Output
(2024-01, 100, NULL), (2024-02, 150, 50.00), (2024-03, 120, -20.00)

Constraints

  • PostgreSQL dialect.

Hints

PostgreSQL
Run checks the 3 visible tests, Submit all 7.