SQLMaxx
Sign in Start free
N040-M4 Tier 3 · Intermediate · medium ecommerce · Brightlane

Return the ID, name, and price of every product, plus the running minimum and running maximum `price` observed from the first product through that product in order of `id`

Part of Aggregate Window Functions (SUM, AVG, COUNT OVER) in SQL

The problem

Brightlane's catalog analyst tracks how the lowest and highest observed prices change as products are reviewed in sequence by id.

Write a query to return the ID, name, and price of every product, plus the running minimum and running maximum price observed from the first product through that product in order of id.

Assumptions:

  • The products table has one row per product with an id, a name, and a price.
  • Products are reviewed in ascending id order. The running minimum at each row is the lowest price seen across every product whose id is less than or equal to that row's id. The running maximum is the highest price seen across the same set.

Output:

  • One row per product, with columns id, name, price, running_min, and running_max.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  id,
  name,
  price,
  MIN(price) OVER (
    ORDER BY
      id
  ) AS running_min,
  MAX(price) OVER (
    ORDER BY
      id
  ) AS running_max
FROM
  products

The shape

MIN(price) OVER (ORDER BY id) and MAX(price) OVER (ORDER BY id) track the lowest and highest price observed from the first product through the current row. Both values update only when a new extreme arrives. The running minimum never increases; the running maximum never decreases. As the catalog is reviewed in id order, the two columns hold the running floor and ceiling of the prices seen so far.

Clause by clause

  • SELECT id, name, price returns each product's identifier, name, and price. The two running extremes are attached.
  • MIN(price) OVER (ORDER BY id) AS running_min returns the smallest price across every row from the start of the table through the current one. On the first row, running_min equals that row's own price, 999. On the second row, running_min stays at 999 because the second price, 1199, is higher. The value drops only when a lower price arrives.
  • MAX(price) OVER (ORDER BY id) AS running_max returns the largest price across the same accumulating set. On the first row, it equals 999. On the second row, it rises to 1199 because that exceeds the previous maximum. The value rises only when a higher price arrives.
  • FROM products reads every product. Both window expressions accumulate over the same growing row set, defined by ORDER BY id inside OVER.

Why running MIN and MAX and not a grouped MIN and MAX

A grouped MIN(price) and MAX(price) would return one number each across the whole catalog, with no row-level detail. The catalog analyst needs to see, at each product, the floor and ceiling reached up to that point in the review sequence. That's a per-row question, and the windowed form answers it without collapsing the rows.

You practiced running MIN and MAX over an ordered window — both values update only as smaller or larger values enter the cumulative set; otherwise they hold steady.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.