← Problems94. Year-over-year revenue growthMediumPySpark
00:00 / 25:00

Year-over-year revenue growth

MediumNot attempted yetAsked at Walmart, Deloitte, TCS

The annual report wants revenue per year and the change against the year before — but the business did not trade every year, and one year's refunds cancelled its sales exactly.

Input

orders order_id bigint ordered_at string -- ISO-8601 UTC, 'YYYY-MM-DDTHH:MM:SSZ' amount double -- negative on a refund

Output

One row per calendar year that has at least one order, with the columns:

  • year — 'YYYY' as a string
  • revenue — that year's total amount
  • prev_revenue — the immediately preceding calendar year's revenue, or null if that year has no orders at all
  • yoy_pct — (revenue - prev_revenue) / prev_revenue * 100

Example

yearrevenueprev_revenueyoy_pct
2023100.0nullnull
2024150.0100.050.0

Constraints

  • The previous calendar year, not the previous row. If 2023 has no orders, 2024 compares against nothing — prev_revenue and yoy_pct are both null, and reaching back to 2022 is wrong.
  • A previous year that nets to zero is a real year: prev_revenue is 0.0, but the percentage is undefined, so yoy_pct is null. Not infinity, not zero.
  • Row order is not graded. Column names and their order are.

Example 1a year of growth

Input · orders
amountorder_idordered_at
6012023-03-01T00:00:00Z
4022023-09-01T00:00:00Z
15032024-02-01T00:00:00Z
Output
yearrevenueprev_revenueyoy_pct
2023100nullnull
202415010050

Example 2the first year has nothing to compare with

Input · orders
amountorder_idordered_at
2512024-05-01T00:00:00Z
Output
yearrevenueprev_revenueyoy_pct
202425nullnull

Topics

window functionsaggregationtime intelligence

Similar problems

Community-reported interview topic. Not an official company question and no affiliation is implied.

solution.py
Loading editor…
Draft not saved yet · Spaces 4 · UTF-8⌘↵ run ⌘⇧↵ submit
Nothing run yet

Run against the public tests, or submit to score against all of them.