CTEs, subqueries, set operations, and relational division

Problem¶

How to break a task into steps?
How to test whether matching rows exist?
How to combine result sets?
How to find entities linked to every required item?

Notions: CTEs, subqueries, set operations, and relational division

SQL and Pandas syntax¶

WITH filtered AS (
    SELECT *
    FROM t
    WHERE value > 0
),
summed AS (
    SELECT
        key,
        SUM(value) AS total
    FROM filtered
    GROUP BY key
)
SELECT *
FROM summed
WHERE total > 100;

SELECT *
FROM a
WHERE EXISTS (
    SELECT 1
    FROM b
    WHERE b.id = a.id
);

SELECT id FROM a
UNION
SELECT id FROM b;

SELECT id FROM a
UNION ALL
SELECT id FROM b;

SELECT id FROM a
INTERSECT
SELECT id FROM b;

SELECT id FROM a
EXCEPT
SELECT id FROM b;

SELECT
    entity_id
FROM facts
GROUP BY entity_id
HAVING COUNT(DISTINCT required_item_id) = (
    SELECT COUNT(*)
    FROM required_items
);

filtered = df.loc[df["value"] > 0]

summed = (
    filtered.groupby("key", as_index=False)
    .agg(total=("value", "sum"))
)

out = summed.loc[summed["total"] > 100]

semi = a.loc[a["id"].isin(b["id"])]

union_distinct = pd.concat([a[["id"]], b[["id"]]]).drop_duplicates()
union_all = pd.concat([a[["id"]], b[["id"]]])
intersect = a.loc[a["id"].isin(b["id"]), ["id"]].drop_duplicates()
except_df = a.loc[~a["id"].isin(b["id"]), ["id"]].drop_duplicates()

Example¶

import sqlite3
import pandas as pd

## SQL

con = sqlite3.connect(":memory:")

con.executescript("""
CREATE TABLE customers (
    id INTEGER,
    name TEXT
);

CREATE TABLE required_products (
    product_id INTEGER
);

CREATE TABLE purchases (
    customer_id INTEGER,
    product_id INTEGER
);

INSERT INTO customers VALUES
    (1, 'Ava'),
    (2, 'Ben'),
    (3, 'Cam');

INSERT INTO required_products VALUES
    (10),
    (20);

INSERT INTO purchases VALUES
    (1, 10),
    (1, 20),
    (2, 10),
    (3, 10),
    (3, 20),
    (3, 20);
""")

sql = """
-- Find customers who bought every required product.
SELECT customer_id
FROM purchases
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (
    SELECT COUNT(*)
    FROM required_products
)"""

pd.read_sql_query(sql, con)
#    customer_id
# 0            1
# 1            3

sql = """
-- Return names for customers who bought every required product.
WITH complete_customers AS (
    SELECT customer_id
    FROM purchases
    GROUP BY customer_id
    HAVING COUNT(DISTINCT product_id) = (
        SELECT COUNT(*)
        FROM required_products
    )
)
SELECT
    c.id,
    c.name
FROM customers AS c
WHERE c.id IN (
    SELECT customer_id
    FROM complete_customers
)
ORDER BY c.id;
"""

pd.read_sql_query(sql, con)
#    id name
# 0   1  Ava
# 1   3  Cam

set_sql = """
-- List products bought by customer 1 but not by customer 2.
SELECT product_id
FROM purchases
WHERE customer_id = 1
EXCEPT
SELECT product_id
FROM purchases
WHERE customer_id =2
"""

pd.read_sql_query(set_sql, con)
#    product_id
# 0          20

## Pandas

customers = pd.DataFrame({
    "id": [1, 2, 3],
    "name": ["Ava", "Ben", "Cam"]
})

required_products = pd.DataFrame({
    "product_id": [10, 20]
})

purchases = pd.DataFrame({
    "customer_id": [1, 1, 2, 3, 3, 3],
    "product_id": [10, 20, 10, 10, 20, 20]
})

needed = required_products["product_id"].nunique()
needed # 2

complete_ids = (
    purchases.groupby("customer_id", as_index=False)
    .agg(product_count=("product_id", "nunique"))
    .loc[lambda x: x["product_count"] == needed, "customer_id"]
)
complete_ids
# 0    1
# 2    3
# Name: customer_id, dtype: int64

customers.loc[customers["id"].isin(complete_ids), ["id", "name"]]
#    id name
# 0   1  Ava
# 2   3  Cam

(
    purchases.loc[purchases["customer_id"] == 1, ["product_id"]]
    .drop_duplicates()
    .loc[lambda x: ~x["product_id"].isin(
        purchases.loc[purchases["customer_id"] == 2, "product_id"]
    )]
    .sort_values("product_id")
)
#    product_id
# 1          20