The Grain of a Table in Python

The grain of a table is what one of its rows means. orders.csv has one row per order line: one product in one order. An order of three products fills three rows, all with the same order_id.

So counting rows is not counting orders. COUNT(*) counts every row, while COUNT(DISTINCT order_id) counts each different order id only once. A data engineer writes down the grain of every table, because a report that counts lines as orders, or repeats an order's total on each of its lines, gives numbers that look fine and are too big.

To change the grain, group the rows. With GROUP BY order_id each order becomes one row, and in that query COUNT(*) counts the rows of each group (the order's lines) while SUM(…) adds them up. The same tools on another column make one row per product:

con.execute(
    "CREATE TABLE product_sales AS "
    "SELECT product, "
        "COUNT(*) AS lines, "
        "SUM(quantity) AS items "
    "FROM 'orders.csv' "
    "GROUP BY product "
)

That table has 8 rows, one per product, just as COUNT(DISTINCT product) on orders.csv gives 8 while COUNT(*) gives 1497. In this lesson the hidden check runs your query, shows the first three rows of fact_orders, then adds up its rows, lines and totals. It must print:

order_lines | orders
1497 | 877
(1 row)
order_id | lines | total
1001 | 3 | 175
1002 | 3 | 64
1003 | 1 | 4
(3 rows)
table_rows | line_count | grand_total
877 | 1497 | 60522
(1 row)

Your task: in query, count the orders next to the lines with COUNT(DISTINCT order_id), named orders. Then finish fact_orders at order grain: group it by order_id, and give each order its number of lines and its total, the sum of quantity * unit_price.

Assignment
Follow the Coding Tutorial to count orders, not lines, and to build fact_orders with one row per order.

Hint
Every line belongs to exactly one order, so after grouping, the lines of all orders still add up to 1,497. A column that another column follows ends with a comma.