SQL at Work #4: Reviews of Categories

This is a practice solution for StrataScratch question ID 10049.

Not every business category attracts the same level of customer interaction. Ranking categories by total reviews helps businesses and investors identify which sectors have the most consumer engagement. This information is valuable for recognizing market demand and competition. For Yelp, these insights can guide content moderation, advertising, and feature development.

SELECT UNNEST(STRING_TO_ARRAY(categories, ';')) AS category,
    SUM(review_count) AS total_reviews
FROM yelp_business
GROUP BY category
ORDER BY total_reviews DESC;

One thing I picked up from this query is the use of UNNEST combined with STRING_TO_ARRAY, not something I reach for often, but really handy when a single column holds multiple values separated by a delimiter.