Case Study 5: EasyDrive Insurance (Queries)

exercises
tutorial
case-study
databases
sql
aggregation
grouping

Practice for 2026-04-06-aggregation-grouping-and-multiple-relation-queries. An analyst at EasyDrive Insurance needs several queries answered against their production data (loaded from a synthetic-data dump), but struggles to write them — this case study writes them on the analyst’s behalf.

Schema

Note this schema has evolved slightly since week6-tutorial-case-study-4-easydrive-insurance — VehicleType has been split into VehicleCodeMapping, VehicleValue and VehicleExcessRange (as transcribed directly from the source materials — the two case studies’ schemas are not perfectly identical).

Customer           [CustomerID, Name, DateOfBirth, Email, Occupation, AddressID]
Address            [AddressID, StreetName, Number, Suburb, Postcode, State, Country]
Vehicle            [VehicleID, VehicleCode, VehiclePurpose, EstYearlyKm]
VehicleCodeMapping [VehicleCode, Make, Model, Year]
VehicleValue       [VehicleCode, MarketValue]
VehicleExcessRange [VehicleCode, MinimumExcess, MaximumExcess]
Policy             [PolicyID, CustomerID, VehicleID, PolicyStartYear, PolicyPurchaseDate, Excess, Premium]

Customer.AddressID references Address.AddressID
Policy.VehicleID references Vehicle.VehicleID
Policy.CustomerID references Customer.CustomerID
Vehicle.VehicleCode references VehicleCodeMapping.VehicleCode
VehicleExcessRange.VehicleCode references VehicleCodeMapping.VehicleCode
VehicleValue.VehicleCode references VehicleCodeMapping.VehicleCode

Task 1a

Return the number of vehicles used for every VehiclePurpose, ordered greatest to least.

SELECT   VehiclePurpose, COUNT(*)
FROM     Vehicle
GROUP BY VehiclePurpose
ORDER BY COUNT(*) DESC;

Task 2

Which CustomerID(s) have at least 2 Policies?

SELECT   CustomerID
FROM     Policy
GROUP BY CustomerID
HAVING   COUNT(*) >= 2;

Task 3

The marketing team wants to find the most common suburb of our customers (return the suburb name and street count, two columns). If there are ties, return all of them, in ascending alphabetical order of suburb name. Assume different suburbs never share a name.

SELECT   Suburb AS 'Suburb Name', COUNT(*) AS 'Street Count'
FROM     Address
GROUP BY Suburb
HAVING   COUNT(*) >= ALL (
    SELECT   COUNT(*)
    FROM     Address
    GROUP BY Suburb
)
ORDER BY Suburb ASC;

>= ALL (...) here means “greater than or equal to every group’s count” — i.e. the maximum. See 2026-04-13-nested-queries-views-and-generative-ai for more on ALL.

Task 4a

A possible fraud was detected — identify all street names containing "et" with at least two customers living on that street.

SELECT   StreetName
FROM     Address
WHERE    StreetName LIKE '%et%'
GROUP BY Postcode, StreetName
HAVING   COUNT(*) >= 2;

This query is incorrect as written: grouping by (Postcode, StreetName) doesn’t account for whether two customers actually live at the same address (e.g. a shared house sharing one AddressID) versus merely the same street. Correctly answering this requires joining Address to Customer and reasoning about which customers share an address — which needs a multi-relation query, covered next week. This is a useful edge case to notice: grouping by the wrong combination of columns can silently produce a plausible-looking but wrong count.

Task 4b

Duplicate counting can happen for many different reasons — check whether the query above counts each street name correctly.

Same underlying issue as 4a — grouping by StreetName alone (dropping Postcode from the GROUP BY) doesn’t fix the shared-address double-counting problem either; both versions can overcount when multiple customers share one AddressID.