E-commerce Product Search Pagination

basic

Build a product search system that shows 20 items per page with consistent pagination. Users should be able to navigate between pages without products jumping around, even when multiple products have the same price or relevance score.

-- Product search pagination system

WITH search_results AS (

SELECT

product_id,

product_name,

brand,

price,

avg_rating,

review_count,

-- Calculate relevance score

(

CASE WHEN product_name ILIKE '%smartphone%' THEN 10 ELSE 0 END +

CASE WHEN brand IN ('Apple', 'Samsung') THEN 5 ELSE 0 END +

avg_rating * 2 +

LOG(review_count + 1)

) as relevance_score

FROM products

WHERE product_name ILIKE '%smartphone%'

AND in_stock = true

AND price BETWEEN 100 AND 2000

),

paginated_results AS (

SELECT

*,

-- ROW_NUMBER ensures unique, sequential positions

ROW_NUMBER() OVER(

ORDER BY

relevance_score DESC, -- Best matches first

avg_rating DESC, -- Higher rated first

review_count DESC, -- More reviews first

price ASC, -- Cheaper first for ties

product_id -- Final tie-breaker

) as page_position,

-- Calculate total results for pagination info

COUNT(*) OVER() as total_results

FROM search_results

)