Day 6 of 10 · Harbour Mutual

Views, CTEs and window functions

Write analysis that reads like the question: save queries as views, build them step by step with CTEs, and compare rows with their neighbours using window functions. Then build the claims triangle.

Theory for today: Reserves and incurred claims, Claims development, Views, CTEs and window functions.

The lesson

Views

A view is a saved query with a name. It holds no data of its own: every time you select from it, its query runs. Views give a business definition, such as earned premium, one home.

CREATE OR REPLACE VIEW active_policies AS
SELECT * FROM policies WHERE status = 'active';

SELECT COUNT(*) FROM active_policies;

CREATE OR REPLACE lets you change it later. Try it in the playground

CTEs: WITH

A common table expression names a step of a query. Several steps, each reading the ones before, turn a tangle of subqueries into a story you can read top to bottom.

WITH paid AS (
    SELECT claim_id, SUM(amount) AS amount
    FROM claim_payments
    GROUP BY claim_id
),
incurred AS (
    SELECT c.cause, COALESCE(paid.amount, 0) + c.reserve AS incurred
    FROM claims c LEFT JOIN paid USING (claim_id)
)
SELECT cause, COUNT(*), ROUND(AVG(incurred), 2)
FROM incurred
GROUP BY cause;

The same answer as Day 4's subqueries, in named steps. Try it in the playground

Window functions

A window function computes over a set of rows related to the current one, without collapsing them as GROUP BY does. OVER (PARTITION BY ... ORDER BY ...) says which rows and in what order.

ROW_NUMBER() numbers the rows 1, 2, 3; RANK() gives ties the same number.
LAG(x) and LEAD(x) read the previous and next row's value.
SUM(x) OVER (ORDER BY ...) is a running total.

SELECT cause, claim_number, reserve,
       RANK() OVER (PARTITION BY cause ORDER BY reserve DESC) AS position
FROM claims
WHERE status = 'open';

Each open claim, ranked within its cause by reserve. Try it in the playground

SELECT EXTRACT(MONTH FROM paid_on) AS month, SUM(amount) AS paid,
       SUM(SUM(amount)) OVER (ORDER BY EXTRACT(MONTH FROM paid_on)) AS paid_so_far
FROM claim_payments
WHERE paid_on BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
GROUP BY 1
ORDER BY 1;

A running total of 2025's claim payments, month by month. Try it in the playground

Windows keep every row; group by collapses them. Filter on a window's result in an outer query or CTE, as WHERE runs before windows are computed.

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 6.1 · Earned premium as a view

Create a view policy_earned_2025 with one row per policy and two columns, policy_id and earned_premium: the premium earned in 2025, to 2 decimals. A policy earns its premium over the days it covers in 2025, divided by the days the premium pays for: 365 for an annual product, the trip's own length for a single trip (product 5). Policies with no cover in 2025 earn 0.

Tables: policies

Brief 6.2 · Loss ratios on earned premium

Compute every product's 2025 loss ratio properly: incurred claims for losses in 2025, divided by premium earned in 2025 (as in brief 6.1). Show the product name and loss ratio, to 2 decimals. Use CTEs; your view from 6.1 can help.

Tables: policies, products, claims, claim_payments, policy_earned_2025 (yours)

Brief 6.3 · The broker league table

For each country, which two brokers have the most active policies? Show the country, broker name, number of active policies and their position in the country (1 or 2).

Tables: policies, brokers, regions

Brief 6.4 · The largest claim for each cause

For every cause, which claim has the highest incurred cost (paid plus reserve)? Show the cause, claim number and incurred cost.

Tables: claims, claim_payments

Brief 6.5 · Who renewed?

What share of each product's policy terms that ended in 2024 were renewed? A term is renewed when the same customer's next term of the same product starts the day after it ends. Leave out single trips (product 5), which do not renew. Show the product name, terms due, terms renewed and the retention rate, to 2 decimals.

Tables: policies, products

Brief 6.6 · The claims triangle

Build Harbour Mutual's paid claims triangle: for each accident year (the year of the loss, 2023 to 2025) and development year (0 for payments in the accident year itself, 1 the year after, and so on, up to 2025), the cumulative amount paid. Include every cell up to 2025, even one with no new payments. Show accident year, development year and cumulative paid.

Tables: claims, claim_payments

6 briefs to go today.

Day 7: Functions and triggers