SQL Playground

A fictional sheet-metal shop, Lame Sheep Inc., in a real Postgres database. Read-only. Write any query you like.

The tour (10 queries)

Joins, date math, and a computed column. The question every shop asks at 7 AM.
GROUP BY two dimensions with date_trunc, and FILTER to count only the finished work.
A window function: SUM(...) OVER (PARTITION BY ... ORDER BY ...) gives a running total without a self-join.
CTE + HAVING. Compares estimated minutes to what the time log actually says, per station.
A crosstab without the tablefunc extension - FILTER clauses do it in plain SQL.
Three CTEs deep: queued work vs. weekly capacity per station, as weeks of backlog.
Window functions for a Pareto view: cumulative share of shipped weight, and each customer’s rank.
Timestamps, intervals, and NULL handling. The report a time-clock system must produce every Monday.
Joins through four tables to put a dollar figure on waste, using material cost per pound.
EXPLAIN ANALYZE on a real query against real indexes. Read it bottom-up: scans first, joins above, the slowest node is your target.

Tables

- id integer · PK
- name text
- segment text
- city text
- state character
- since date
- id integer · PK
- job_no text
- customer_id integer → customers.id
- project text
- priority smallint
- status text
- quoted_on date
- released_on date
- due_on date
- shipped_on date
- id integer · PK
- job_id integer → jobs.id
- part_no text
- type text
- material_id integer → materials.id
- width_in numeric
- height_in numeric
- length_in numeric
- qty integer
- sqft numeric
- weight_lb numeric
- line text
- lined boolean
- id integer · PK
- metal text
- gauge numeric
- lb_per_sqft numeric
- cost_per_lb numeric
- id integer · PK
- part_id integer → parts.id
- station_id integer → stations.id
- seq smallint
- est_minutes integer
- status text
- id integer · PK
- code text
- name text
- stage smallint
- id integer · PK
- operation_id integer → operations.id
- employee_id integer → employees.id
- started_at timestamptz
- ended_at timestamptz
- minutes integer
- qty_done integer
- machine_id integer → machines.id
- id integer · PK
- name text
- role text
- shift smallint
- hired_on date
- active boolean
- home_machine_id integer → machines.id
- id integer · PK
- employee_id integer → employees.id
- punched_in timestamptz
- punched_out timestamptz
- id integer · PK
- part_id integer → parts.id
- employee_id integer → employees.id
- reason text
- sqft numeric
- logged_at timestamptz
- id integer · PK
- job_id integer → jobs.id
- shipped_at timestamptz
- truck text
- pieces integer
- weight_lb numeric

today() returns the shop's "today". The shop is live (see Shop Floor): machines, punches and shipments keep arriving, so results change as you watch.

How this is safe

Queries run as a Postgres role that can only SELECT from the shop schema. It can't see the site's own tables.
The app connects as that role, with a password rotated on every boot, so there's no SET ROLE back up.
Read-only transaction, 3-second statement timeout, one statement per run, 500-row cap, 30 runs a minute.
Everything is fictional data generated from a fixed seed.