Module 01 Activity

Scenario

You have just been given read access to the analytics database and asked, "Can you pull our June numbers?" You have not been told what any table contains. Before you answer, you are going to write the note that makes every later query defensible.

Task

Work only from the starter database. Do not guess at anything you can query.

1. List every table and, for each one, write one sentence beginning "One row is...". That sentence is

the grain, and it is the thing most analysts skip.

1. Record the row count of each table.
2. For orders and order_items, work out how many line items a typical order has, and the minimum and

maximum.

1. Find the column that joins orders to order_items, and confirm every order actually has items.
2. Write one sentence describing what happens to the row count when you join those two tables.

Deliverable

A schema note - one page, plain text or markdown - containing a table with columns table | one row is | row count, followed by three sentences: the join key, the grain change on joining, and one thing you could not determine from the data alone.

Check your work

Your counts should match these exactly:

Six tables in total. All 1,000 orders have line items - COUNT(DISTINCT order_id) in order_items is 1,000, so no order is missing its basket. Orders carry 3 to 4 items, averaging 3.4. That 3.4 is exactly why the join produces 3,400 rows rather than 1,000: the grain changes from one row per order to one row per line item.

What good looks like

The note is useful to a colleague who has never opened the database. A note that says "orders table: contains orders" has recorded nothing. A note that says "one row is one order; one row in order_items is one product line within an order, so joining multiplies orders by 3.4" has recorded the single fact that prevents the most expensive mistake in this course.

Mark complete