FinOps: global RANK on order totals

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 20 minutes. Part of the Pro drill bank.

Ticket from FinOps: 'The ranking tile runs for a long time. It ranks all orders ever made in one big list.' The query ranks every order against every other order, but the report only needs the rank within each day. Without a PARTITION BY, the database sorts all rows together.

Rank the non-cancelled orders by order_total within each order day. The editor has a global RANK() with no PARTITION BY, so the rank keeps growing across days. Fix the window so the rank starts at 1 again for every day. Return order_id, order_date (the date of created_at), order_total and revenue_rank. revenue_rank is the rank by order_total from high to low within the day. Exclude cancelled orders. Order by order_date, revenue_rank, order_id. To pass, the ranks must be right and the efficiency score must be at least 70.

Requirements

  • Exclude orders with order_status = 'cancelled'.
  • Use RANK() with PARTITION BY the order date and ORDER BY order_total DESC.
  • Return order_id, order_date, order_total, revenue_rank.
  • Order by order_date, revenue_rank, order_id.

Constraints

  • order_date is CAST(created_at AS DATE).
  • The cancelled filter stays in the query.
  • RANK() gives the same rank to equal totals, and the next rank is skipped.

Examples

Input: order_id | order_date | order_total ORD-0010 | 2026-08-02 | 900.00 ORD-0020 | 2026-08-02 | 450.00 ORD-0030 | 2026-08-03 | 900.00 Output: order_id | order_date | order_total | revenue_rank ORD-0010 | 2026-08-02 | 900.00 | 1 ORD-0020 | 2026-08-02 | 450.00 | 2 ORD-0030 | 2026-08-03 | 900.00 | 1 Why this passes: Ranks reset per order_date. ORD-0030 is rank 1 on Aug 3 even though ORD-0010 had the same total on Aug 2.

Topics: finops, windows, rank.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

FinOps: global RANK on order totals

Production ticket: Revenue rank uses RANK() OVER (ORDER BY order_total) across all orders instead of per day.

Rank non-cancelled orders by `order_total` **within each order day**: - order_id, order_date, order_total, revenue_rank - order_date from `created_at` - exclude cancelled - revenue_rank starts at 1 each day (RANK by order_total DESC within the day) - ordered by order_date, revenue_rank, order_id The editor has a global RANK with no PARTITION BY. Fix the window and keep the filter. Pass = correct ranks **and** efficiency score ≥ 70.