Day 3 of 10 · Harbour Mutual

Joins and relationships

Combine tables: follow the keys from claims to policies, customers, products and brokers with JOIN, keep unmatched rows with LEFT JOIN, and find what is missing.

Theory for today: Losses and claims, Distribution, Joins and relationships.

The lesson

Each fact lives in one table: a claim knows its policy_id, a policy its customer_id and product_id. This keeps data consistent, but most questions need facts from several tables. A join matches rows through those keys.

INNER JOIN

SELECT c.claim_number, c.cause, p.policy_number
FROM claims c
JOIN policies p ON p.policy_id = c.policy_id
LIMIT 5;

c and p are aliases. JOIN (inner join) keeps only rows that match on both sides. Try it in the playground

When the key has the same name in both tables, USING (policy_id) is a shorter way to write the same join. Chain joins to walk further along the data model:

SELECT c.claim_number, pr.name AS product, cu.name AS customer
FROM claims c
JOIN policies p USING (policy_id)
JOIN products pr USING (product_id)
JOIN customers cu USING (customer_id)
LIMIT 5;

Claim to policy, policy to product and to customer. Try it in the playground

LEFT JOIN and what is missing

A left join keeps every row of the left table; where nothing matches on the right, the right-hand columns are null. That makes it the tool for finding what is missing.

SELECT b.name, p.policy_number
FROM brokers b
LEFT JOIN policies p ON p.broker_id = b.broker_id AND p.status = 'active'
ORDER BY b.name
LIMIT 10;

Conditions on the right table belong in the ON: in the WHERE they would drop the unmatched rows. Try it in the playground

SELECT COUNT(*)
FROM policies p
WHERE NOT EXISTS (
    SELECT 1 FROM claims c WHERE c.policy_id = p.policy_id
);

An anti-join: rows with no match. LEFT JOIN ... WHERE other.id IS NULL does the same. Try it in the playground

Before joining, ask what one row of the result should be. Joining claims to policies gives a row per claim; joining policies to claims, with a left join, a row per claim plus one for each claim-free policy.

Briefs from the business

0 of 5 done

Write SQL to answer each brief, run it, then check your answer against Harbour Mutual's. Your database is the same one as in the playground.

Starting PostgreSQL in your browser…

Brief 3.1 · December's losses

For every claim whose loss happened in December 2025, the claims director wants the claim number, the product's name and the customer's name.

Tables: claims, policies, products, customers

Brief 3.2 · Active policies never claimed on

How many active policies have never had a claim? Answer with a single number.

Tables: policies, claims

Brief 3.3 · Brokers in Poland

List Harbour Mutual's brokers in Poland with the region each works in: broker name and region name.

Tables: brokers, regions

Brief 3.4 · Customers without cover

How many customers have no policy in force today (no active policy)? Answer with a single number.

Tables: customers, policies

Brief 3.5 · Motor renewals in Poland

The Polish motor team wants the active motor policies of customers living in Poland whose cover ends in the next 30 days (31 December 2025 to 30 January 2026): policy number, customer name and end date.

Tables: policies, products, customers, regions

5 briefs to go today.

Day 4: Aggregations and reporting