The Lost Dogs of King County

When you lose a dog in Seattle, every corner, side lot, and townhome courtyard becomes a place where she could be hiding. Every white-coated dog makes you yearn for the one you’re searching for and makes you think reflexively about the worst possible scenario.

I am now attuned to the pain in each post on Facebook (like the one below) and Nextdoor about a neighbor’s missing dog or a found canine in the streets. 

From those missing dog posts, I began to pick up on some common trends that I wanted to answer through data. Luckily, King County collects and updates data on adoptable, found, and lost dogs. 

The questions I wanted to answer were:

1. Which dog breeds are most likely to be adoptable?
2. In which cities are there the most lost dogs?

Adoptable Breeds

To answer my first question, I first had to set up a local database for the first time. I downloaded Postgres and Postico 2 to allow management and transformation of the data with SQL. 

Then, I downloaded the dataset from King County as a CSV file. When I initially pasted the data into Postico 2, there were some issues with the data (ex., different cases for the same cities, different entries for the same pet, etc.). I cleaned them up by searching for and removing duplicates. 

To answer the question of which dogs are likely to be lost, I wrote a SQL query with a common table expression (CTE) to select the dogs that were adoptable.

WITH adoptable AS (
        SELECT
		status,
		breed
		
	FROM
		pets
	
	WHERE
		status = 'ADOPTABLE' AND type = 'Dog'
		
	ORDER BY breed
)

Then, I used the COUNT() aggregate keyword to tally the number of dogs by breed and ordered the list by the breeds most available for adoption. (Please adopt dogs if you can!)

SELECT
	breed,
	COUNT(breed) AS count

FROM adoptable

GROUP BY breed

ORDER BY count DESC

The end result was this table:

From just the data pulled in mid-August 2025, the most common adoptable dogs were German Shepherds and Pit Bull mixes.

City of (Lost) Dogs

The second question I had was where lost dogs are commonly found. 

I first had to clean up the data, as many lost dogs arrive in shelters from unspecified locations. I created a CTE that coalesced all NULL fields as “Unknown” and pulled just dogs that were categorized as lost from the original dataset.

WITH lost_dogs AS (
	SELECT
		COALESCE(city, 'Unknown') as cities,
		type	
	FROM pets
	WHERE type = 'Dog'

Then, because the city field entries could vary (ex., KENT and Kent,), I wrote another CTE to make them all just initial-capitalized.

lost_cities AS (
	SELECT 
		INITCAP(cities) as city_all,
		type
	
	FROM lost_dogs
)

Then, I wanted to count the number of lost dogs in each city without the unknown locations and prepare the query to rank the cities by number of lost dogs.

rank AS (
	SELECT 
		city_all,
		COUNT(*) AS count
		
	FROM lost_cities
	
	WHERE city_all != 'Unknown'
		
	GROUP BY city_all, type
	
	ORDER BY count DESC

Finally, I ranked the top five cities in King County by the number of lost dogs found in their jurisdiction.

SELECT
	city_all,
	DENSE_RANK () OVER (
		ORDER BY count DESC) AS dense_rank,
	count
		
FROM rank

LIMIT 5

The result was this table:

The snapshot of data from this date showed that Kent was by far the city in King County with the largest number of lost dogs found in it. However, it’s important to note that more than 50 entries for lost dogs had no specified city.

Although SQL is limited in terms of searching for the “why,” I am curious to know what accounts for the high population of adoptable German Shepherds and Pit Bulls, as well as why so many lost dogs are from Kent. 

What other pet-related data would you like to see? Please let me know in the comments!

Thank you for following along! You can see the GitHub repository here. 

P. S. If you’re wondering, we reunited with our dog thanks to some kind strangers in Capitol Hill!