Categorisation¶

Exercise categorisation-annex.pdf · PDF

You open a chest in a game and a sword drops. Do you read its drop rate, "0.37%"? No. You look at the colour of its name. Common, Rare, Epic, Legendary. One word and you know whether to keep it.

Somebody decided where "Rare" ends and "Epic" begins. That person turned thousands of possible numbers into four labels, so that you could decide in half a second. On this page you do the same job for games: 199 different prices become five bands, and every game gets a tier for the size of its audience.

From numbers to rarity tiers¶

Count how many different prices the table has:

SELECT COUNT(DISTINCT price) FROM games;   -- 199

199 different prices. Draw one bar per price and you get 199 bars: unreadable. Nobody reads "drop rate 0.37%". Everybody reads "Epic".

A category is a label that puts a value into a group. Categorising is replacing many different values by a few labels.

A bin (or band) is one of those groups when the original value is a number: a range with a lower and an upper edge.

The analogy for the whole page is the rarity tier: a category is a number that has been made readable.

CASE WHEN: the sorting rule¶

CASE WHEN is a column that is calculated with conditions: for each row it gives the label of the first condition that is true.

The skeleton:

CASE WHEN condition_1 THEN 'label 1'
     WHEN condition_2 THEN 'label 2'
     ELSE 'label for the rest' END AS new_column

Each WHEN ... THEN ... line is a branch. Three rules decide everything:

flowchart TB
    R["<b>One row</b><br>price = ?"] --> W1{"price IS NULL?"}
    W1 -- "true" --> L1["'Free'"]
    W1 -- "not true" --> W2{"price under 5?"}
    W2 -- "true" --> L2["'Under 5'"]
    W2 -- "not true" --> W3{"price under 15?"}
    W3 -- "true" --> L3["'5 to 15'"]
    W3 -- "not true" --> W4{"price under 30?"}
    W4 -- "true" --> L4["'15 to 30'"]
    W4 -- "not true" --> E["<b>ELSE</b><br>'30 or more'"]

The free game that looked expensive¶

Try the simplest possible rule on three games you know:

SELECT game, price,
       CASE WHEN price < 5 THEN 'Under 5' ELSE '5 or more' END AS band
FROM games WHERE game IN ('Half-Life 2', 'Dota 2', 'Portal') ORDER BY game;

Dota 2 is free. It got the band 5 or more. Its price is NULL, NULL < 5 is not true, so the row fell to ELSE. The game with the most owners in the whole table, and free, labelled as expensive. No error message.

Building price_band¶

Five bands, prices in US dollars. The NULL case goes first:

SELECT CASE WHEN price IS NULL THEN 'Free'
            WHEN price < 5  THEN 'Under 5'
            WHEN price < 15 THEN '5 to 15'
            WHEN price < 30 THEN '15 to 30'
            ELSE '30 or more' END AS price_band,
       COUNT(*) AS n
FROM games WHERE game IS NOT NULL
GROUP BY price_band ORDER BY MIN(price) NULLS FIRST;

%%{init: {"themeVariables": {"xyChart": {"plotColorPalette": "#4051b5"}}}}%%
xychart-beta
    title "Games per price band"
    x-axis ["Free", "Under 5", "5 to 15", "15 to 30", "30 or more"]
    y-axis "Games" 0 --> 12000
    bar [3094, 11613, 9046, 2367, 565]

Five rows, adding up to 26,685. Five bars instead of 199.

Notice the branch WHEN price < 15 THEN '5 to 15'. It does not say price >= 5 AND price < 15. It does not need to: a row only reaches that branch if price < 5 was not true. The order already does that job.

Break it twice, on purpose¶

The best way to understand the rules is to damage the query and watch.

Break 1 — move a branch to the top. Put WHEN price < 30 THEN '15 to 30' above all the others:

Every game under 30 dollars stops at the first branch. Under 5 and 5 to 15 have vanished: 23,026 games now carry the wrong label.

Break 2 — delete the Free branch. Put the order back and remove the WHEN price IS NULL line:

The 3,094 free games fell to ELSE and joined the 565 expensive ones: 3,659 games "at 30 or more". Both broken queries run without a single error. This is the silent error of the types lesson again.

Sorting categories in their logical order¶

Why ORDER BY MIN(price) and not ORDER BY price_band? Because a label is text, and text sorts like a dictionary:

Alphabetically, 15 to 30 comes first, because the character 1 comes before 3 and 5. To get the logical order you sort by something that still is a number: the smallest price inside each band. NULLS FIRST puts the free games, which have no price, at the top.

Building owners_tier¶

The second category describes the audience, using the owners_min built in the previous lesson. Three tiers:

A column created in a SELECT cannot be reused in that same SELECT, so the owners_min expression is written out again inside the CASE:

CASE WHEN REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint < 20000
       THEN 'Niche'
     WHEN REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint < 1000000
       THEN 'Mid'
     ELSE 'Hit' END AS owners_tier,

Added to the clean query (the block is now called step3) and counted with SELECT owners_tier, COUNT(*) AS n FROM step3 GROUP BY owners_tier ORDER BY MIN(owners_min);:

Two out of three games in the table are Niche. Only 623 out of 26,685 are a Hit.

Crossing two categories¶

Categories exist to be crossed. With both columns in step3, one query answers "which price band produces more hits?":

SELECT price_band, COUNT(*) AS games,
       SUM(CASE WHEN owners_tier = 'Hit' THEN 1 ELSE 0 END) AS hits,
       ROUND(100.0 * SUM(CASE WHEN owners_tier = 'Hit' THEN 1 ELSE 0 END) / COUNT(*), 1) AS hit_pct
FROM step3 GROUP BY price_band ORDER BY MIN(price) NULLS FIRST;

The SUM(CASE ... THEN 1 ELSE 0 END) is a counting trick: each hit adds 1, everything else adds 0. The share of hits goes from 0.4% among the cheapest paid games to 10.4% among the most expensive.

Where do the edges go?¶

An edge is the value where one bin ends and the next begins.

Why < 5 and not <= 5? Look at the most common prices:

SELECT price, COUNT(*) FROM games GROUP BY price ORDER BY 2 DESC LIMIT 3;

Prices pile up just under the round number: 4.99, 9.99. So the edge goes on the round number, and everybody agrees which side 4.99 falls on.

Messy categories¶

Text categories get dirty too. How many publishers are there?

SELECT COUNT(DISTINCT publisher) AS as_written,
       COUNT(DISTINCT LOWER(publisher)) AS ignoring_case
FROM games;

LOWER turns a text into small letters. Ignoring capitals, 88 "publishers" disappear: they were second spellings of one that already existed. Look at two of them:

SELECT publisher, COUNT(*) FROM games
WHERE LOWER(publisher) IN ('big fish games', 'capcom')
GROUP BY publisher ORDER BY 1;

For you, two publishers. For a database, four. The same tool merges them:

CASE WHEN LOWER(publisher) = 'big fish games' THEN 'Big Fish Games'
     ELSE publisher END AS publisher

That gives one Big Fish Games with 285 games. In the class table we do not fix the 88 spellings: we know about them, and that goes in the report. Knowing a limit and writing it down is also a decision.

Try it: bands and tiers¶

Open SQL → SQL Lab, database Dades classe, schema public. Run one statement at a time.

Step 1 — price_band. Run the five-band query on games. You should see five rows in the order Free, Under 5, 5 to 15, 15 to 30, 30 or more, adding up to 26,685.

Step 2 — Break it twice. Make the two changes described above, one at a time. Before fixing each one, write down the counts and one sentence saying where the games went.

Step 3 — Add both columns to the clean query. Open games_step2 under SQL → Saved Queries, rename the block step3, and add your price_band and the owners_tier expression. Mind the comma before each new CASE. Check with the owners_tier count: Niche 17,921, Mid 8,141, Hit 623.

Step 4 — Save it. Press Save, change the name to games_step3, choose Save as new.

Step 5 — Cross them. Keep the WITH step3 block and run the hits query. You should be able to say which band has the highest share of hits and which has the lowest.

Key takeaways¶

• A category makes a number readable: 199 prices become 5 bins. The number stays in its own column, because a label cannot be turned back into a number.
• CASE WHEN tries its branches top to bottom and stops at the first true one. The order is part of the logic.
• ELSE takes everything else, including NULL. Name the NULL case first, with IS NULL.
• A wrong order or a missing branch gives no error: check the counts of every band.
• Sort categories by a number (ORDER BY MIN(price)), not by the label, which sorts alphabetically.
• The edges are your decision. Put them on round numbers, use one direction, and write them down.
• Text categories get dirty too: Capcom and CAPCOM are two values until you merge them.

Quick check¶

One of today's bands was not a price at all: Free was a guess about what an empty cell means. Were we allowed to make it? And what about 23,838 empty scores? Next: Imputation.