The Q4 portfolio review
The board meets in January. Build Harbour Mutual's portfolio review for the last quarter of 2025, section by section, entirely in SQL: growth, profitability, claims, distribution and the largest risks still open.
Theory to have at hand: Measuring a portfolio, Reserves and incurred claims, Distribution.
The review
Every quarter the analytics team presents the portfolio review: how much business was written, whether it made money, what the claims look like and where the risks are. The board reads the numbers; the regulator may ask how each was derived. Everything you need is in the course: joins (Day 3), aggregation (Day 4), CTEs and window functions (Day 6), and the insurance behind them (the theory pages).
Each brief below is one section of the review. Write it as you would at work: readable, with CTEs for the steps, so that next quarter someone can run it again with new dates.
| Section | Question the board asks |
|---|---|
| C.1 Growth | Are we writing more business than a year ago? |
| C.2 Profitability | Which lines make money? |
| C.3 Claims | What came in during the quarter, and what is still open? |
| C.4 Distribution | Which brokers bring the business, and what do they cost? |
| C.5 Large risks | Which open claims could hurt most? |
| C.6 Headline | The year on one line, saved so it can be run again. |
When every section passes, print the page: with your queries and their results, it is the review, and its audit trail.
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 C.1 · Growth
For each line of business (motor, home, travel), the premium written in the fourth quarter of 2024 and of 2025, and the growth in percent to one decimal. Show line, Q4 2024 premium, Q4 2025 premium and growth.
Tables: policies, products
Brief C.2 · Profitability
Each line's loss ratio for 2025: the incurred cost of claims for losses in 2025, divided by the premium earned in 2025, to 2 decimals. Show line and loss ratio.
Tables: policies, products, claims, claim_payments
Brief C.3 · Claims in the quarter
For each line, the claims reported in the fourth quarter of 2025: how many, their incurred cost, and how many are still open. Show line, claims, incurred and still open.
Tables: claims, claim_payments, policies, products
Brief C.4 · Distribution
The five brokers who wrote the most premium in the fourth quarter of 2025: broker name, number of policies, premium written and the commission it earns them (premium × their commission rate, to 2 decimals). Largest first.
Tables: policies, brokers
Brief C.5 · Large open risks
The five open claims with the largest reserves: claim number, product, cause and reserve, largest first.
Tables: claims, policies, products
Brief C.6 · The headline, saved
Save the year's headline as a view portfolio_review, one row with the columns written_premium (policies starting in 2025), earned_premium (earned in 2025, to 2 decimals), incurred (claims for losses in 2025), loss_ratio (incurred ÷ earned, to 2 decimals), open_claims and reserves (the open claims now, and their reserves).
Tables: policies, claims, claim_payments, portfolio_review (yours)
6 sections of the review to go.
Want analytics training for your team? I build hands-on labs like this, and the data platforms behind them. Work with me(opens in a new tab)