topn

pig build pkg topn;		# build deb

pig repo add pgdg -u    # add pgdg repo and update cache

pig install topn;		# install via package name, for the active PG version

pig install topn -v 18;   # install for PG 18
pig install topn -v 17;   # install for PG 17
pig install topn -v 16;   # install for PG 16
pig install topn -v 15;   # install for PG 15
pig install topn -v 14;   # install for PG 14

CREATE EXTENSION topn;

CREATE EXTENSION topn;

-- Materialize top products by date
CREATE TABLE popular_products (
  review_date date UNIQUE,
  agg_data jsonb
);

INSERT INTO popular_products
SELECT review_date, topn_add_agg(product_id)
FROM customer_reviews GROUP BY review_date;

-- Get the top-1 product for each day
SELECT review_date, (topn(agg_data, 1)).*
FROM popular_products ORDER BY review_date;

-- Top 10 across a time range (merging daily summaries)
SELECT (topn(topn_union_agg(agg_data), 10)).*
FROM popular_products
WHERE review_date >= '2000-01-01' AND review_date < '2000-02-01'
ORDER BY 2 DESC;