Count the Rows in Python

This chapter builds data quality checks: small queries that catch bad data before a report shows wrong numbers. A check counts the rows that break one rule, so 0 means it passes. The starting code loads the week's files from orders/ into raw_orders, every column as text, plus a filename column that says which file each row came from. It also gives you report(rule, bad), which prints rule: PASS when bad is 0 and rule: FAIL (bad) otherwise.

The first check on any load is the row count per file. An export that breaks halfway still writes a valid CSV, just a short one: the load runs without an error, and the report quietly shows a terrible day. GROUP BY filename makes one group per file, and COUNT(*) counts its rows. The first query starts with CREATE TABLE file_rows AS, so running it with con.execute(query) saves its result as a table.

A fixed limit such as "at least 10 rows" would also flag a quiet day. So the rule compares each file with the week: a file with fewer rows than half the average fails. The average comes from a subquery, a query in brackets that gives one value. WHERE can't use AVG by itself, because it looks at one row at a time. For example, the customers who spent more than average:

SELECT customer_id
FROM 'customers.csv'
WHERE spent > (SELECT AVG(spent) FROM 'customers.csv')

A hidden step adds orders/2026-05-08.csv with a single order: 5076,118,Mug,1,12,paid,2026-05-08 08:14:00. The files of 05-01 to 05-07 hold 10, 12, 12, 9, 13, 10 and 12 rows, so the average is 79 / 8 = 9.875 and only 05-08 is under half of it. Your program prints the first line below; the check prints the rest:

no file under half the average: FAIL (1)
filename | n_rows
orders/2026-05-01.csv | 10
…
orders/2026-05-08.csv | 1
(8 rows)
Without the file of 05-08:
no file under half the average: PASS

Your task: finish the first query so it builds file_rows, one row per file with its filename and n_rows, and run it. Then make the second query count the files with fewer rows than half the average, and report that count as "no file under half the average".

Assignment
Follow the Coding Tutorial to count the rows of each file and flag the files under half the week's average.

Hint
The table file_rows exists only after con.execute(query) runs the first query. The second query gives one number: con.execute(query).fetchone()[0].