Union and Intersection of SQL query with Jaccard similarity included
<pre><code>CREATE TABLE Customer (cid INTEGER PRIMARY KEY, cname VARCHAR(20) NOT NULL);
CREATE TABLE Product  (pid INTEGER PRIMARY KEY, pname VARCHAR(20) NOT NULL,
    msrp INTEGER NOT NULL);
CREATE TABLE Purchase (cid INTEGER, pid INTEGER, date DATE, 
    price INTEGER NOT NULL,
    PRIMARY KEY (cid, pid, date),
    FOREIGN KEY (cid) REFERENCES Customer(cid),
    FOREIGN KEY (pid) REFERENCES Product(pid));
</code></pre>

<p>Consider only customers who have made at least one purchase. For each pair of such
customers compute their Jaccard similarity with respect to the products they have purchased. That is,
if customer A has purchased set a of products and customer B has purchased set b of products,
their Jaccard similarity is the size of the intersection of the two sets divided by the size of
their union. For every pair of customers, output the two cids and the Jaccard similarity. Each pair
should be output only once, i.e. if (1; 3; j) is a matching pair of cids with Jaccard similarity j, you
should return only (1; 3; j) and not (3; 1; j). Sort your output in descending order by Jaccard similarity,
i.e. the most similar pair should be listed rst. Note that this question asks about products and not
product purchases: if a customer has purchased a product more than once, this only counts as one
product for the purposes of this computation.</p>