I have prepared this demo to show you what the output of this analysis could look like. So letโ€™s jump into it!

I imagine that your raw data must look like something like this. It surely has many more columns, I am keeping this demo short. I uploaded this data from a spreadsheet I created.

Animal ID Breed State Insured Insurance cost ($)
1 Labrador NSW Yes 350
2 Bulldog QSLD No 0
3 Poodle NSW Yes 500
4 German Shepherd QSLD Yes 430
5 Greyhound QSLD Yes 490

Based on this raw data, I can perform the data analytics transformations required to answer your questions. Such as:

Overall Top 5 dogs

1. Top 5 dog breeds, based on # of breeds insured

1.1 On a country level

State Rank Breed Share insured
Whole Country 1 Poodle 55%
Whole Country 2 German Shepherd 50%
Whole Country 3 Bulldog 39%
Whole Country 4 Labrador 23%
Whole Country 5 Greyhound 15%

1.2 By state

New South Wales
State Rank Breed Share insured
NSW 1 Labrador 51%
NSW 2 Poodle 45%
NSW 3 German Shepherd 28%
NSW 4 Bulldog 26%
NSW 5 Greyhound 25%
Queensland
State Rank Breed Share insured
QSLD 1 Labrador 44%
QSLD 2 Poodle 42%
QSLD 3 Bulldog 41%
QSLD 4 German Shepherd 38%
QSLD 5 Greyhound 29%

2. Top 5 most expensive dog breeds based on insurance cost

2.1 On a country level

State Rank Breed Average Insurance Cost
Whole Country 1 Labrador $600
Whole Country 2 German Shepherd $530
Whole Country 3 Greyhound $500
Whole Country 4 Bulldog $420
Whole Country 4 Poodle $420

2.2 On a state level

New South Wales

State Rank Breed Average Insurance Cost
NSW 1 Bulldog $580
NSW 2 German Shepherd $520
NSW 3 Greyhound $430
NSW 4 Labrador $390
NSW 5 Poodle $370

Queensland

State Rank Breed Average Insurance Cost
QSLD 1 Greyhound $590
QSLD 2 Bulldog $560
QSLD 3 Labrador $500
QSLD 4 German Shepherd $410
QSLD 5 Poodle $350

And so on for all the questions you have :)