E-commerce Product Search with Pagination

basic

An e-commerce site needs to display search results with pagination. Users can navigate through pages of products, and each page shows exactly 20 items. The system needs unique row positions for consistent pagination.

-- E-commerce product pagination system

WITH paginated_products AS (

SELECT

product_id,

product_name,

brand,

price,

relevance_score,

-- ROW_NUMBER for unique pagination positions

ROW_NUMBER() OVER(

ORDER BY relevance_score DESC, price ASC, product_id

) as page_position

FROM products

WHERE product_name ILIKE '%laptop%'

AND in_stock = true

AND price BETWEEN 500 AND 3000

)

SELECT

product_id,

product_name,

brand,

price,

page_position,

-- Calculate which page this item appears on

CEIL(page_position / 20.0) as page_number,

-- Position within the page (1-20)

((page_position - 1) % 20) + 1 as position_on_page

FROM paginated_products

WHERE page_position BETWEEN 21 AND 40 -- Page 2

ORDER BY page_position;

product_id | product_name | brand | price | page_position | page_number | position_on_page

-----------|------------------------|-------|--------|---------------|-------------|------------------

1247 | MacBook Pro 14" | Apple | 1999.00| 21 | 2 | 1

1248 | Dell XPS 13 | Dell | 1299.00| 22 | 2 | 2

1249 | ThinkPad X1 Carbon | Lenovo| 1599.00| 23 | 2 | 3

1250 | Surface Laptop 5 | MS | 1199.00| 24 | 2 | 4

... | ... | ... | ... | ... | ... | ...