-- Tests joins with DISTINCT operations
CREATE TABLE products_dist(prod_id INTEGER, prod_name VARCHAR, "category" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE sales_dist(sale_id INTEGER, prod_id INTEGER, customer VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO products_dist VALUES (1, 'Widget', 'Tools', 1000), (2, 'Gadget', 'Electronics', 2000);

Affected Rows: 2

INSERT INTO sales_dist VALUES (1, 1, 'Alice', 1000), (2, 1, 'Bob', 2000), (3, 2, 'Alice', 3000), (4, 1, 'Alice', 4000);

Affected Rows: 4

SELECT DISTINCT p.category FROM products_dist p INNER JOIN sales_dist s ON p.prod_id = s.prod_id ORDER BY p.category;

+-------------+
| category    |
+-------------+
| Electronics |
| Tools       |
+-------------+

SELECT DISTINCT s.customer, p.category FROM sales_dist s INNER JOIN products_dist p ON s.prod_id = p.prod_id ORDER BY s.customer, p.category;

+----------+-------------+
| customer | category    |
+----------+-------------+
| Alice    | Electronics |
| Alice    | Tools       |
| Bob      | Tools       |
+----------+-------------+

SELECT p.prod_name, COUNT(DISTINCT s.customer) as unique_customers FROM products_dist p LEFT JOIN sales_dist s ON p.prod_id = s.prod_id GROUP BY p.prod_id, p.prod_name ORDER BY unique_customers DESC;

+-----------+------------------+
| prod_name | unique_customers |
+-----------+------------------+
| Widget    | 2                |
| Gadget    | 1                |
+-----------+------------------+

DROP TABLE products_dist;

Affected Rows: 0

DROP TABLE sales_dist;

Affected Rows: 0