Fulfledge

Analyse real data

INPUT · Slides

Getting to know the shop

01 / 05

From here on it is analysis

By now you can take, narrow, sort, gather and join. In this chapter there is no new way of writing.

What you do instead is practise building a query out of something you want to know. You already have every tool.

02 / 05

The three tables of Breeze Mart

The setting is a made-up online shop called Breeze Mart. There are three tables.

  • members … the members (name area age plan joined_on)
  • items … the goods (name category price cost stock)
  • orders … the purchase history (member_id item_id quantity ordered_on)

price is what it sells for, cost is what it was bought in for, and stock is how many are left.

03 / 05

Tables connect through numbers

orders holds neither item names nor member names. All it holds is numbers.

  • member_id in orders points at id in members
  • item_id in orders points at id in items

So to see "who bought what" you join on those numbers.

SELECT o.id, m.name, i.name  FROM orders o  JOIN members m    ON o.member_id = m.id  JOIN items i    ON o.item_id = i.id  ORDER BY o.id LIMIT 3;

Result

1 | Aoi | teacup
2 | Hinata | pencil case
3 | Haruto | cleaning cloth

04 / 05

Read a question as three questions

A loose question like "what is selling well?" does not become a query as it stands. Split it three ways.

  • what are you counting? … orders? units? money?
  • which table holds it? … will one do? do you need a join?
  • how do you gather it? … one number overall? per group?

Settle those three and the rest is copying it down.

05 / 05

Look inside first

The first move in any analysis is always to look at what is there. Check what columns exist and how many rows before you frame a question.

When in doubt, take a few rows and gaze at them. Let us write some.

SELECT * FROM items  ORDER BY id  LIMIT 3;

Result

1 | cutting board | kitchen | 1200 | 700 | 24
2 | trivet | kitchen | 600 | 300 | 60
3 | teacup | kitchen | 800 | 400 | 44