sql

Second highest order per customer

SQL & Data Modeling · Window Functions · medium · Original Uplift question · PostgreSQL · 12 min

Using the orders table, return one row per customer containing their second highest order amount. Customers with fewer than two orders should not appear. Order the output by customer_id.

Fixture

orders

order_id, customer_id, amount, created_at

create table orders (
  order_id int,
  customer_id int,
  amount numeric,
  created_at date
);

insert into orders values
  (1, 101, 120.00, '2026-01-03'),
  (2, 101, 250.00, '2026-01-18'),
  (3, 102, 80.00, '2026-01-05'),
  (4, 102, 95.00, '2026-02-01'),
  (5, 102, 140.00, '2026-02-20'),
  (6, 103, 60.00, '2026-01-11');

Evaluation criteria

Uses a window function to rank each customer's orders
Filters to the second rank only
Excludes customers with a single order
Sorted by customer_id

Detailed grading feedback appears after you submit.

Source

Original Uplift question based on a common interview pattern.

Your answer

Your query

You get one graded attempt here without an account. Create a free account for unlimited attempts, saved progress and community write-ups.

Answers stay hidden until you ask. Reveal the full model write-up when you are ready to compare.

Second highest order per customer

Fixture

Evaluation criteria

Source

Your answer

How other people solved this