20. String Splitting and Aggregation
Beginner Mode
Objective
Given a table product_tags containing columns id (unique identifier for each product) and tags (a comma-separated string of tags associated with the product), write an SQL query to count the occurrences of each tag across all products. The result should display each tag along with its corresponding count, ordered first by the count in descending order and then alphabetically by the tag.
Additional information
- The
tagscolumn contains multiple tags separated by commas. - Ensure that the result includes all unique tags from the
tagscolumn. - The output should include the tag and the number of times it appears across all products.
- Tags are case-sensitive (i.e., 'Tag' and 'tag' would be considered different tags).
Examples
Example 1:
Output:
Input:
| product_tags | |
|---|---|
| id | tags |
| 1 | electronics,mobile,smartphone |
| 2 | electronics,laptop,gaming |
| 3 | mobile,accessories,charging |
| 4 | electronics,gaming,console |
| 5 | mobile,smartphone,accessories |
| tag | tag_count |
|---|---|
| electronics | 3 |
| mobile | 3 |
| accessories | 2 |
| gaming | 2 |
| smartphone | 2 |
| charging | 1 |
| console | 1 |
| laptop | 1 |
Code Environment
Sign in or try as guest to run your code.
Essential
SQL 0/33
Spark 0/20
Snowflake 0/22
Python 0/24
Need more practice in this area? Explore more questions →
Vercel
Revolut
Accenture
Adobe
Google
LinkedIn
Samsung
Datadog
Wix
Dropbox
Meta
OpenAI
Hulu
Uber
X
DoorDash
Anthropic
Amazon
ActivisionBlizzard
Crypto.Com
Zscaler
DeutscheBank
Apple
GoDaddy
GitLab
BMW
PayPal
Snowflake
AMD
Twilio
Atlassian
JPMorgan
NVIDIA
IBM
Databricks
Coinbase
Cisco
Robinhood
Twitter
Microsoft
Palantir
Netflix
VMware
Cloudflare
Stripe
Lyft
Salesforce
GitHub
Bloomberg
Airbnb
Walmart
SAP
HashiCorp
Instacart
Mastercard
Intel
Visa
Tesla