Day 4 of 10 · Harbour Mutual

Aggregations and reporting

Summarise: count, sum and average with aggregate functions, group with GROUP BY, filter groups with HAVING, and combine results with subqueries. Then compute Harbour Mutual's loss ratios.

Theory for today: Premiums, Measuring a portfolio, Aggregation and grouping.

The lesson

Aggregates

SELECT COUNT(*) AS policies,
       SUM(annual_premium) AS premium,
       ROUND(AVG(annual_premium), 2) AS average,
       MIN(start_date), MAX(start_date)
FROM policies;

An aggregate turns many rows into one value. COUNT(column) counts only non-null values. Try it in the playground

GROUP BY

SELECT status, COUNT(*) AS policies
FROM policies
GROUP BY status;

One result row per group. Every column in the SELECT is either grouped or aggregated. Try it in the playground

WHERE and HAVING

WHERE filters rows before grouping; HAVING filters groups after, so it can use aggregates.

SELECT cause, COUNT(*) AS claims
FROM claims
WHERE loss_date >= DATE '2025-01-01'
GROUP BY cause
HAVING COUNT(*) >= 50
ORDER BY claims DESC;

Claims from 2025 only (where), then causes with at least 50 (having). Try it in the playground

Subqueries

A query can be used as a table. This is how to combine totals that come from different tables, such as premium and claims:

SELECT c.claim_id, c.reserve, COALESCE(paid.amount, 0) AS paid
FROM claims c
LEFT JOIN (
    SELECT claim_id, SUM(amount) AS amount
    FROM claim_payments
    GROUP BY claim_id
) paid USING (claim_id)
LIMIT 5;

Sum the payments per claim first, then join: joining first would repeat each claim once per payment. COALESCE turns the null of an unpaid claim into 0. Try it in the playground

Aggregate at the level you need before joining. A claim with three payments, joined to its payments, becomes three rows, and its reserve is counted three times.

With incurred claims (paid plus reserve, see Losses and claims) and premium, you can compute the loss ratio (see Measuring a portfolio).

Briefs from the business

0 of 6 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 4.1 · Premium written in 2025

For each product, how many policy terms started in 2025, and what premium did they write? Show product name, number of policies and written premium.

Tables: policies, products

Brief 4.2 · Claims and their cost in 2025

For each line of business, how many claims were there for losses in 2025, and what did they cost on average (incurred: paid plus reserve, to 2 decimals)?

Tables: claims, policies, products, claim_payments

Brief 4.3 · The main causes of claims

Which causes have produced at least 100 claims in all three years? Show the cause, the number of claims and their average incurred cost (to 2 decimals).

Tables: claims, claim_payments

Brief 4.4 · The busiest brokers

Which brokers have more than 125 active policies? Show the broker's name and number of active policies.

Tables: policies, brokers

Brief 4.5 · Customers who claim often

Which customers have made more than three claims across all their policies? Show the customer's name and number of claims.

Tables: claims, policies, customers

Brief 4.6 · Which products lost money in 2025?

Which products had a loss ratio above 0.70 in 2025? Use the incurred cost of claims for losses in 2025, divided by the premium billed in 2025 (a simple stand-in for earned premium; see Premiums). Show the product name and loss ratio, to 2 decimals.

Tables: products, policies, premium_transactions, claims, claim_payments

6 briefs to go today.

Day 5: Constraints and indexes