Not Null and Unique in Python

Some columns are keys: order_id names one order, and customer_id says whose order it is. A key must pass two rules. It is not null: no row leaves it blank. And an id like order_id is unique: no value comes twice. Without these checks, a repeated order is counted twice in revenue, and an order with no customer drops out of every per-customer report. Nobody notices until a number looks wrong.

Not null: DuckDB reads a blank CSV field as NULL, which means no value at all. COUNT(*) counts every row, but COUNT(column) counts only the rows where the column has a value. So the difference is the number of blanks. Here it is on another file and column:

query = (
    "SELECT COUNT(*) - COUNT(age) "
    "FROM 'customers.csv' "
)
print(con.execute(query).fetchone()[0])   # 14: the customers with no age

Don't look for blanks with = NULL. A comparison with NULL is never true, so it matches nothing. COUNT(*) FILTER (WHERE customer_id IS NULL) is another correct way.

Unique: GROUP BY order_id makes one group per id, and each group holds every copy of that id. HAVING filters groups the way WHERE filters rows. It runs after the grouping, so it can test COUNT(*), the number of rows in a group. For example, on customers.csv, GROUP BY city HAVING COUNT(*) > 1 keeps the cities that have more than one customer. A query in brackets after FROM is a subquery, and SELECT COUNT(*) FROM ( … ) counts its rows. That turns the list of repeated ids into one number.

On the week's files, your checks print the first two lines below. The blank is order 5058 in 05-06's file. The repeated ids are 5027 (twice in 05-03's file), 5040 (05-04 and 05-05) and 5062 (05-06 and 05-07). The last two lines come from the lesson's check. It keeps only the clean files of 05-01 and 05-02 and runs your second query again.

customer_id is not null: FAIL (1)
order_id is unique: FAIL (3)
Only the files of 05-01 and 05-02:
order_id is unique: PASS

Your task: the starting code loads the week into raw_orders, every column as text. It also gives you report(rule, bad), which prints PASS when bad is 0 and FAIL with the count otherwise. Write the not-null check on customer_id and the uniqueness check on order_id, one clause per line. Pass each query's one value, con.execute(query).fetchone()[0], to report.

Assignment
Follow the Coding Tutorial to write the two checks, not null on customer_id and unique on order_id, and report each one.

Hint
COUNT(column) skips NULLs. For the second rule, the inner query groups the rows by order_id and keeps the groups with more than one row. The outer COUNT(*) counts them.