CREATE TABLE products(product_id SERIAL PRIMARY KEY, name TEXT); CREATE TABLE categories(category_id SERIAL PRIMARY KEY, name TEXT); CREATE TABLE categories_products( category_id INTEGER REFERENCES categories(category_id), product_id INTEGER REFERENCES products(product_id) );

INSERT INTO products(name) VALUES ('Macbook Pro'), ('Macbook Air'), ('iPhone 11'), ('Google Pixel 4'), ('iPad Mini 4'), ('iPad'); INSERT INTO categories(name) VALUES ('Laptops'), ('Phones'), ('Tablets'); INSERT INTO categories_products VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 5), (3, 6);

SELECT * from categories;

SELECT * FROM products;

SELECT categories_products.category_id, COUNT(*) OVER() AS all_products FROM categories_products;

SELECT DISTINCT categories.category_id, categories.name, COUNT(categories_products.product_id) OVER(PARTITION BY categories_products.category_id) AS products, COUNT(categories_products.product_id) OVER() AS all_products FROM categories INNER JOIN categories_products ON categories.category_id = categories_products.category_id

SELECT categories.category_id, categories.name, COUNT(*) AS products, SUM(COUNT(*)) OVER() AS all_products FROM categories_products INNER JOIN categories ON categories.category_id = categories_products.category_id GROUP BY categories.category_id, categories.name;