Data Analytics SQL Lab

Learn PostgreSQL the way an analyst at an insurer uses it: ten hands-on days at Harbour Mutual, a fictional insurer, with the insurance theory behind every query and PostgreSQL running in your browser.

Start with Day 1 Open the SQL playground Explore the data model

Meet Harbour Mutual

A mid-sized insurer selling motor, home and travel insurance in the Netherlands, Poland and Germany, directly and through brokers. You join its analytics team: every query you write answers a question the business really asks. All of its data is generated; no real company or person is described.

Harbour Mutual's logical data model: eleven tables in four groups. Regions have customers and brokers. A customer holds policies; each policy is for one product, is sold directly or by a broker, has coverages, premium transactions and claims. Each claim has payments and documents.

Insurance theory

The business before the SQL: what a policy, a premium, a loss and a claim are, and how an insurer measures its portfolio.

How an insurer works

Risk pooling: premiums in, claims and expenses out.

Policies

Policyholder, coverage, limit, deductible, policy period, renewal and lapse.

Premiums

Written, earned and unearned premium, and exposure.

Losses and claims

The event versus the request for payment, and a claim's life.

Reserves and incurred claims

Paid versus incurred, case reserves and IBNR.

Measuring a portfolio

Frequency, severity, loss ratio, combined ratio and retention.

Claims development

Development triangles and a first look at the chain ladder.

Distribution

Direct sales, brokers and commission.

Fraud and data quality

Red flags, and how constraints protect the numbers.

Data, privacy and regulation

Personal data, least privilege and regulatory reporting.

SQL theory

The ideas behind each day's SQL: how a query is evaluated, how joins and aggregates behave, how PostgreSQL plans a query, and how transactions and roles protect the data.

Relational databases

Tables, keys, types, null, and the parts of SQL.

How a query is evaluated

The logical order of a SELECT, three-valued logic and types.

Joins and relationships

One to many, many to many, the kinds of join and the fan-out trap.

Aggregation and grouping

Aggregates, nulls, WHERE against HAVING, ratios and subqueries.

Integrity and normalisation

Constraints, normal forms and B-tree indexes.

Views, CTEs and window functions

Saved queries, named steps, and calculations over related rows.

Functions and triggers

PL/pgSQL, volatility, BEFORE and AFTER, and when to use a constraint.

How PostgreSQL plans a query

Statistics, scans, join methods and sargable conditions.

JSON and extensions

json against jsonb, containment, GIN indexes and trigrams.

Transactions and security

ACID, MVCC, roles, row-level security and backups.

Ten days of SQL

Each day teaches one block of SQL, then hands you briefs from Harbour Mutual's business. Your answers are checked against the company's data.

Day 1 · The environment

Tour Harbour Mutual's database and keep a watchlist.

Databases, tables, running SQL, viewing data

Day 2 · SELECT mastery

Motor policies in Poland renewing in the next 30 days.

WHERE, ORDER BY, LIMIT and OFFSET, built-in functions

Day 3 · Joins and relationships

Claims with their policy, product, customer and broker.

INNER and LEFT JOIN, anti-joins, reading the data model

Day 4 · Aggregations and reporting

Premium, claim frequency and loss ratio by product and country.

SUM, COUNT, AVG, GROUP BY, HAVING, subqueries

Day 5 · Constraints and indexes

Stop impossible policies from entering the books.

Keys, UNIQUE, CHECK, NOT NULL, indexes

Day 6 · Views, CTEs and window functions

Broker league table, retention and the claims triangle.

WITH, views, ROW_NUMBER, RANK, LAG, OVER()

Day 7 · Functions and triggers

Earned premium, and claims outside the policy period rejected.

PL/pgSQL functions, BEFORE and AFTER triggers

Day 8 · Query optimisation

Make the month-end claims report fast on 200,000 claims.

EXPLAIN ANALYZE, sequential, index and bitmap scans

Day 9 · JSONB and extensions

Search adjuster notes and flag claims for fraud review.

JSONB, arrays, GIN indexes, pg_trgm fuzzy search

Day 10 · Security, roles and backup

A reporting role, and brokers who see only their own clients.

Roles, GRANT, row-level security, export and restore

Capstone · The Q4 portfolio review

Harbour Mutual's quarterly review for the board: growth, loss ratios, claims, brokers and large risks, all in SQL.

Everything from the ten days, together

An unhandled error has occurred. Reload 🗙