Skip to main content
2026-07-29 16:01
Database

Window Functions

QueryValidateHard
Try Interactive Demo

What you will learn

  • ROW_NUMBER() OVER (ORDER BY ...) assigns sequence — test stable ordering across ties (pick deterministic tiebreaker)
  • Window functions execute AFTER WHERE but BEFORE ORDER BY + LIMIT — can't filter on window results in WHERE, use subquery
  • RANK() vs DENSE_RANK() vs ROW_NUMBER(): each handles ties differently — test with tied data, not only unique rows

Description

Rank products by price within each category.

Test Scenario

-- Your query:
SELECT
  name,
  category,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM products;

-- Expected: Products ranked within their category

Testing Tips

  • •OVER() defines the window
  • •PARTITION BY groups without collapsing
  • •ROW_NUMBER(), RANK(), DENSE_RANK()
// Playwright API Testing - Database
import { test, expect } from '@playwright/test';


test('Database query via API', async ({ request }) => {
  const response = await request.post('/api/database/query', {
    data: {
      sql: 'SELECT * FROM users WHERE id = ?',
      params: [1]
    }
  });


  expect(response.ok()).toBeTruthy();
  const { rows } = await response.json();
  expect(rows.length).toBeGreaterThan(0);
});

Challenge ID: sql-window-functions

2 frameworks available