--SELECT d1.name, d1.good_with_children, d1.good_with_other_dogs, d1.good_with_strangers, d2.age, d2.gender, d2.spayneuter
--FROM @kaggle.warcoder_dog_breeds_details.dog_breeds d1
--JOIN @kaggle.stealthtechnologies_predict_new_york_dog_bites.dog_bites_data d2 ON d1.name = d2.breed
--WHERE d1.trainability > 3
--ORDER BY max_life_expectancy asc

SELECT d1.name, d1.good_with_children, d1.good_with_other_dogs, d1.good_with_strangers,
       COUNT(d2.breed) AS breed_occurrences
FROM @kaggle.warcoder_dog_breeds_details.dog_breeds d1
JOIN @kaggle.stealthtechnologies_predict_new_york_dog_bites.dog_bites_data d2 ON d1.name LIKE ('%' || d2.breed || '%')
WHERE d1.trainability > 3
GROUP BY d1.name, d1.good_with_children, d1.good_with_other_dogs, d1.good_with_strangers
ORDER BY breed_occurrences desc