/*
Adobe SQL Interview Question

You have a table Tags with two columns, id and tag,
Each row in the Tags table contains an id and a tag. The tag column stores a string of hash (#) separated numbers, where each number represents a different tag.
For example, a tag value of 200#100#300 means the tags 200, 100, and 300 are associated with the given id.

Your task is to process the Tags table to generate a report that shows the count of each unique tag value (value) for each id.
The output should include the following columns:

id: The identifier from the Tags table.
value: The individual tag values extracted from the tag column.
count: The number of times each value appears for the corresponding id.
*/

-- Schema Setup
create table tags(id int, tag varchar(20));
insert into tags values (1, '#200#100#100#300');
insert into tags values (2, '#200#200#300#400');

-- Solution 
WITH RECURSIVE split_tags AS (
    SELECT
        id,
        SUBSTRING_INDEX(tag, '#', 1) AS value,
        SUBSTRING(tag, LENGTH(SUBSTRING_INDEX(tag, '#', 1)) + 2) AS remaining
    FROM tags
    WHERE tag IS NOT NULL
    UNION ALL
    SELECT
        id,
        SUBSTRING_INDEX(remaining, '#', 1) AS value,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, '#', 1)) + 2) AS remaining
    FROM split_tags
    WHERE remaining IS NOT NULL AND remaining != ''
)

SELECT
    id,
    value,
    COUNT(1) AS count
FROM split_tags
WHERE value != ''
GROUP BY id,value
ORDER BY id;