The Workbench, documented.

🧪 The environment

What actually runs when you press Run.

The Workbench is a notebook of blocks — note, SQL, and Python — and the whole engine runs inside your browser tab. Nothing is installed on your machine and no code or data is sent to a server to execute:

Deliberate limits (they are features, not bugs): Python has no network access, no pip install, no input(), and no threads; memory is what the browser tab allows. Everything a project needs ships with the runtime.

⌨️ The editor

A real code editor in every SQL and Python block — with suggestions from your own data.

Code is coloured as you type, brackets and quotes close themselves, and suggestions appear as you type: SQL keywords and DuckDB functions, your dataset tables, their columns (each labelled with its table), and any view you have created — as soon as the block that creates it has run. Right after FROM or JOIN it suggests tables; elsewhere, columns. In Python it suggests the lp helpers, dataset names inside lp.table("…"), column names inside df["…"], and common pandas methods after a dot.

Everything you type is saved as you go, exactly as before. The same editor is used in the Academy's career projects and practice challenges.

🗂️ Getting data in

Upload → attach → alias. The alias is the table name.

Data enters the platform once, at the top, and flows down to every member:

1. Upload (curators): an admin uploads a CSV (or an Excel file, whose first sheet is converted to CSV) in the dataset manager. Rows and columns are scanned automatically and the dataset appears in the public library.
2. Attach (brief authors): a project brief attaches one or more datasets, each under an alias. Aliases are validated because they become SQL table names — budget works, Budget Data! is rejected.
3. Use (you): open the Workbench on a claimed brief and every attached dataset is already loaded as a table under its alias. The left rail lists each one with a ✓ and its row count.

-- The aliases in the left rail ARE your tables. First look:
SELECT * FROM budget LIMIT 5;

Referring to a dataset by name

Code never uses a dataset's title from the library ("Public Procurement Data"). Each brief gives every dataset it attaches a short alias — lowercase letters, digits and underscores, starting with a letter, up to 41 characters — and that alias is its name in SQL and in Python. You will find it in the Workbench's left rail, under "Datasets in scope" on the brief page (alias: …), in the Data Explorer's list, or by asking the engine:

SHOW TABLES;          -- every dataset alias, plus the views you have made
DESCRIBE budget;      -- its columns and their types
SUMMARIZE budget;     -- per column: min, max, average, distinct values, % missing
SELECT * FROM budget LIMIT 5;

lp.datasets()                         # ['budget', 'departments']
df = lp.table('budget')               # the dataset's file as a DataFrame
df = pd.read_csv('/data/budget.csv')  # the same file, read directly
v = await lp.sql('SELECT * FROM clean_budget')   # views: only through lp.sql

Looking at your data without writing SQL

🔍 Data Explorer — switch to it at the top of the left rail, or click a dataset in the rail. It shows the whole table: search across every column, click a column header to sort (click again to reverse), and page through 25, 50 or 100 rows at a time, with the row count.

📊 Profile & Quality — a tab in the right panel. For each column: its type, how much is missing and how many distinct values it has; for the table: duplicate rows, columns with 5% or more missing, and columns that never change. A good first stop before any cleaning.

Members do not upload their own files in the current version — every project runs on curated data, which is what makes review and auto-checks possible. Have a dataset worth adding? Mail it to the address in the dataset library sidebar.

🐤 SQL guide

The dialect is DuckDB. If you learned MySQL or Postgres, 95% carries over — the differences are listed at the end.

Selecting and filtering

SELECT category, budgeted_amount, actual_amount
FROM   budget
WHERE  actual_amount > budgeted_amount   -- conditions can compare columns
ORDER BY actual_amount - budgeted_amount DESC
LIMIT 5;

Aggregating and grouping

SELECT category,
       COUNT(*)                                   AS line_items,
       SUM(actual_amount)                         AS spent,
       SUM(actual_amount) - SUM(budgeted_amount)  AS variance
FROM   budget
GROUP BY category
HAVING SUM(actual_amount) > 1000000   -- HAVING filters groups; WHERE filters rows
ORDER BY variance;

Dates

SELECT strftime(due_date, '%Y')                        AS year,
       date_diff('day', due_date, DATE '2027-01-01')   AS days_overdue
FROM   receivables
WHERE  due_date BETWEEN DATE '2022-01-01' AND DATE '2022-12-31';

Write date literals as DATE 'YYYY-MM-DD'. date_diff('day', a, b) counts from a to b; strftime(date, format) formats it.

Banding with CASE

SELECT CASE WHEN receivable_amount >= 30000 THEN 'large'
            WHEN receivable_amount >= 10000 THEN 'medium'
            ELSE 'small'
       END AS size_band,
       COUNT(*) AS invoices
FROM   receivables
GROUP BY size_band;   -- grouping by the alias works

Joins

SELECT b.category, d.dept_head, SUM(b.actual_amount) AS spent
FROM   budget b
JOIN   departments d ON b.category = d.category
GROUP BY b.category, d.dept_head
ORDER BY spent DESC;
-- LEFT JOIN keeps rows with no match (departments that spent nothing)

You can join any datasets attached to the brief and any views you have created — as many as the question needs. Datasets that are not attached to the brief are not loaded, so they cannot be joined.

Subqueries and CTEs

WITH dept AS (
  SELECT category, SUM(actual_amount) AS spent
  FROM   budget
  GROUP BY category
)
SELECT category, spent,
       ROUND(spent * 100.0 / (SELECT SUM(spent) FROM dept), 1) AS pct
FROM   dept
ORDER BY spent DESC;

Views — saving a query under a name

CREATE OR REPLACE VIEW clean_budget AS
SELECT * FROM budget WHERE actual_amount >= 0;

SELECT SUM(actual_amount) FROM clean_budget;  -- reads through the view

Coming from MySQL? The differences that bite

🐍 Python guide

Real Python in the tab, with a tiny bridge to your data called lp.

# Cross-check a SQL number independently in pandas
df = await lp.sql("SELECT * FROM invoices")
overdue = df[df.is_overdue]

print("overdue invoices:", len(overdue))
print(f"overdue amount: {overdue.receivable_amount.sum():,}")

Output rules, in order: everything you print appears as stdout; a DataFrame that is the last expression of the block renders as a table, index first, every cell as pandas prints it (first 50 rows, with the true total shown); a Series, a number or any other last value is shown the way Python writes it (its repr, as in Jupyter); matplotlib figures, pandas .plot() included, appear as images — up to two plots per run. A trailing semicolon hides the last value. Errors show only the lines of your own code.

import matplotlib.pyplot as plt

cash = await lp.sql("""
  SELECT cash_type, SUM(inflow) - SUM(outflow) AS net_cash
  FROM cash_flow GROUP BY cash_type
""")
plt.bar(cash.cash_type, cash.net_cash)
plt.title("Net cash by activity")
plt.show()

Available out of the box: pandas, numpy, matplotlib, and the Python standard library. Not available, on purpose: network requests, pip installs, file system, input(), threads. If a script needs the internet, it belongs on your own machine — the Workbench is for the analysis.

▶️ Running code & the pipeline

One block, the whole notebook, and the badge that proves it.

▶ Run — executes one block (Ctrl/⌘ + Enter inside the editor does the same). The result is saved with the block, so your outputs are still there tomorrow.

▶▶ Run all — executes every included SQL and Python block top to bottom and stops at the first error. If everything passes, the work earns a ⛓ Reproducible stamp, shown on the published page — proof the notebook runs clean start to finish, not just in the order you happened to click.

🎯 Checks — tasks can carry an auto-check: run it and your current result is compared to the expected answer, pass or fail with the actual value shown. A check is either a SQL query (it sees your views and lp.save tables) or a Python expression on your variables, so run your notebook first. Reviewers see the same check summary.

Errors — appear under the block, verbatim from the engine. Read them; they usually name the problem (an aggregate in WHERE, a missing table, a typo in a column). An error in a Run all halts the pipeline at that block so you always know where it broke.

🚀 From Workbench to published work

The whole loop, end to end.

1. Claim — pick a brief; the Workbench opens with its datasets loaded and its tasks in the 📋 drawer.
2. Work — add note, SQL, and Python blocks; reorder freely; toggle any block out of the final piece. Included blocks become the README.
3. Verify — ▶▶ Run all until green, run the 🎯 checks, cross-check a key number two ways.
4. Submit — your work enters the review queue. A human reviews it; changes may be requested with notes.
5. Published — the repo goes public on your portfolio, readable logged-out, with block-level comments — and XP and coins land in your account.