Synthetic variables¶

Exercise synthetic-variables-annex.pdf · PDF

You buy three things at the supermarket and look at the receipt. The price of each product was on the shelf. But the line that says "3 × 1.20 = 3.60", and the TOTAL at the bottom? Nobody stuck those on a shelf. The till worked them out.

Your data is the same. The file about Steam games tells you how many people own each game and what it costs. It does not tell you how much money each game moved, or what share of the catalogue is free. Those answers are not in the table. You have to make them. In the crafting game this is the moment you stop sorting loot and start crafting: new items, built out of the ones you already have.

Columns nobody collected¶

A synthetic variable is a column that nobody collected. It is computed from the columns that were.

"Synthetic" does not mean fake. It means put together. The line total on a receipt is not a lie: it is 3 × 1.20, and anyone can check it. A synthetic variable is honest for the same reason: it comes with a recipe.

You have already made some without calling them that. In Categorisation you built price_band out of price. In Imputation you built is_free and has_metascore. Today we name the idea and learn the three ways to do it. We will keep the receipt for the whole page:

flowchart LR
    R["<b>Rows of the table</b><br>one game per row"]
    R --> C["<b>Calculated column</b><br>the line total:<br>26,685 rows in, 26,685 rows out"]
    R --> M["<b>Metric</b><br>the TOTAL:<br>26,685 rows in, 1 row per group out"]
    R --> W["<b>Window function</b><br>the share of the receipt:<br>26,685 rows in, 26,685 rows out,<br>each with its group's value"]

First, make the cleaning permanent¶

Every fix you wrote in this unit was a SELECT. It showed a clean result and vanished when you closed the tab. The table games never changed. Before crafting anything new, you store the cleaning as a real table.

You already know the tool from Datasets and destination: CREATE TABLE ... AS SELECT saves the result of a query as a new table. The class script games_clean.sql puts every cleaning step of the unit into one statement. Read it and you will recognise every line:

DROP TABLE IF EXISTS games_clean;
CREATE TABLE games_clean AS
SELECT
  number,
  TRIM(game) AS game,
  COALESCE(NULLIF(TRIM(developer), ''), 'Unknown') AS developer,
  COALESCE(NULLIF(TRIM(publisher), ''), 'Unknown') AS publisher,
  CASE WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{1,2}, [0-9]{4}$' THEN TO_DATE(release_date, 'Mon DD, YYYY')
       WHEN release_date ~ '^[A-Z][a-z]{2} [0-9]{4}$'             THEN TO_DATE(release_date, 'Mon YYYY')
  END AS release_date,
  COALESCE(price, 0)::numeric(8,2) AS price,
  (price IS NULL) AS is_free,
  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,
  REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint AS owners_min,
  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,
  average_playtime, median_playtime,
  metascore,
  (metascore IS NOT NULL) AS has_metascore
FROM games WHERE game IS NOT NULL;
ALTER TABLE games_clean ADD COLUMN release_year int
  GENERATED ALWAYS AS (EXTRACT(YEAR FROM release_date)::int) STORED;

The last line is the first synthetic variable of the day.

A generated column is a column the database computes by itself from other columns of the same row, and keeps up to date.

Nobody types a release_year. PostgreSQL reads release_date and writes the year next to it:

SELECT game, release_date, release_year
FROM games_clean ORDER BY number LIMIT 3;

Check that your table is complete:

SELECT COUNT(*) FROM games_clean;

The answer is 26,685: the 26,688 rows of games minus the three games with no name.

Then register the table so Superset can chart it: Datasets → + Dataset → Database Dades classe, Schema public, Table games_clean → Create dataset (the arrow next to Create and explore dataset).

The line total: a calculated column¶

A calculated column is a new column whose value is worked out row by row, using only the values of that same row.

On the receipt it is "3 × 1.20 = 3.60". The till needs nothing but that one line to print it.

Our line total: how much money did a game move, roughly? Take the owners and multiply by the price. We call it est_revenue, for estimated revenue.

SELECT game, owners_min, price, owners_min * price AS est_revenue
FROM games_clean ORDER BY est_revenue DESC LIMIT 5;

Read the first figure out loud: one thousand four hundred and ninety-nine million. About 1.5 billion dollars. A number that big deserves a second look, and we will give it one at the end of this page.

In SQL Lab that column lives only inside your query. To have it ready for every future chart, add it to the dataset:

Other calculated columns follow the same pattern. The price with 21% VAT is price * 1.21: one row in, one value out.

The TOTAL: a metric¶

A metric is a calculation over many rows that gives back one value per group. It is always an aggregate: SUM, AVG, COUNT, MIN, MAX.

On the receipt it is the TOTAL. The lines disappear and one number is left. You met aggregates in SQL Lab: groups; a metric is an aggregate with a name, saved in the dataset so you never retype it.

Add two, in the same editor: tab Metrics → Add item, twice.

Then Save → OK.

Look at pct_free. CASE WHEN is_free THEN 1 ELSE 0 END turns every game into a 1 or a 0. Adding them up counts the free ones. Dividing by COUNT(*) makes it a share. The 100.0, with its decimal point, stops PostgreSQL from doing whole-number division and answering 0.

A metric has no fixed size. It answers for whatever group you hand it. For the whole table:

SELECT SUM(owners_min * price) AS total_est_revenue,
       ROUND(100.0 * SUM(CASE WHEN is_free THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_free
FROM games_clean;

And with a GROUP BY owners_tier, the same two metrics answer three times:

SELECT owners_tier,
       SUM(owners_min * price) AS total_est_revenue,
       ROUND(100.0 * SUM(CASE WHEN is_free THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_free
FROM games_clean GROUP BY owners_tier ORDER BY owners_tier;

Almost three in ten Hits are free. And the Niche tier earned... zero? Hold that thought.

The share of the receipt: window functions¶

Sometimes you want both things at once: every row, and the total next to it. "This line is 40% of your receipt." With GROUP BY you cannot: the rows are gone.

A window function computes a value over a group of rows and writes it next to every row, without collapsing them. You recognise it by the word OVER.

Read OVER () as "over all the rows, without collapsing them". Here it gives the share of each price band:

SELECT price_band, COUNT(*) AS games,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_games
FROM games_clean GROUP BY price_band ORDER BY games DESC;

COUNT(*) is the line. SUM(COUNT(*)) OVER () is the TOTAL, printed on every line: 26,685 five times. Divide one by the other and you have a share of the total in one query, with no subquery.

A window can also be cut into groups.

PARTITION BY is the GROUP BY of a window: it splits the rows into groups, but the rows stay.

This query compares each game with the average price of its own release year:

SELECT game, release_year, price,
       ROUND(AVG(price) OVER (PARTITION BY release_year), 2)         AS year_avg,
       ROUND(price - AVG(price) OVER (PARTITION BY release_year), 2) AS vs_year_avg
FROM games_clean ORDER BY owners_min DESC, number LIMIT 5;

Five rows, five games, and each one carries the average of its year. PUBG cost 22.30 dollars more than the average 2017 game. vs_year_avg is a synthetic variable built from an aggregate calculation.

What est_revenue is not¶

Now the second look we promised. Here is the share of games and the share of estimated revenue per tier:

SELECT owners_tier, COUNT(*) AS games,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_games,
       ROUND(100.0 * SUM(owners_min * price) / SUM(SUM(owners_min * price)) OVER (), 1) AS pct_revenue
FROM games_clean GROUP BY owners_tier ORDER BY pct_revenue DESC;

623 games out of 26,685 take two thirds of the money. That is a real and interesting pattern.

But 17,921 games earned nothing? No. Remember where owners_min came from: the bottom of a range. The original file says "0 .. 20,000", and the bottom of that range is 0. Zero owners times any price is zero.

The variable is only as good as the columns it was made from. So est_revenue must travel with its label:

Try it¶

Use games_clean in SQL Lab (database Dades classe, schema public). The original file, video_games.csv, is on the Resources page.

Step 1 — Add the calculated column est_revenue and the metrics total_est_revenue and pct_free to your dataset, following the tables above.

Step 2 — Predict first, then run: which tier has the highest pct_free? Check with the GROUP BY owners_tier query.

Step 3 — Finish the sentence with the last query: "The Hit tier is __% of the games and __% of the estimated revenue."

Step 4 — Rank inside a group. RANK() is a window function that numbers the rows of each group. Who are the most-owned games of 2017?

SELECT game, owners_min,
       RANK() OVER (PARTITION BY release_year ORDER BY owners_min DESC) AS position
FROM games_clean WHERE release_year = 2017 ORDER BY position LIMIT 4;

You should get PLAYERUNKNOWN'S BATTLEGROUNDS (1), Unturned (2), and Robocraft and Brawlhalla sharing position 3: they tie at 10,000,000.

Step 5 — Write one reason why est_revenue is not what a game really earned. If you can, you understood the lesson.

Key takeaways¶

• A synthetic variable is a column nobody collected: it is computed from the ones that were. It is honest as long as it carries its recipe.
• CREATE TABLE ... AS SELECT makes the cleaning permanent in games_clean (26,685 rows). A generated column such as release_year is computed by the database itself.
• A calculated column gives one value per row (owners_min * price). A metric gives one value per group (SUM(owners_min * price)).
• A metric must use the full expression: Superset's calculated columns do not exist in the PostgreSQL table.
• A window function (OVER, PARTITION BY) writes a group value next to every row. COUNT(*) / SUM(COUNT(*)) OVER () is a share of the total.
• est_revenue is a floor, not an income: a variable is only as good as the columns it was made from.

Quick check¶

games_clean now exists, with columns that were never in the file. Next you rebuild the same cleaning without writing a line of SQL: Power Query, where you click and the tool writes the recipe for you.