Meta: Trending Reels Tags Split Out of Comma-Separated Lists
An older Reels table stores each reel's tags as one comma-separated string such as ipl,cricket,mumbai.
- Difficulty: Hard
- Topics: Strings, Subqueries, Dates
- Dialect: MySQL
- Problem: #768
Problem statement
An older Reels table stores each reel's tags as one comma-separated string such as ipl,cricket,mumbai. The Reels discovery team wants this week's trending tags from it without waiting for a backfill.
Split every list into its tags (a tag repeated in one list counts once for that reel). Compare this week, 2025-08-11 to 2025-08-17, with last week, 2025-08-04 to 2025-08-10 (both inclusive), by the number of reels carrying each tag. A tag is trending when it is on at least 3 reels this week and this week's count is at least double last week's. Return tag, this_week and last_week (0 if none) for the trending tags, ordered by this_week descending, then tag. A NULL or empty tags has no tags.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: ReelPost
| Column | Type |
|---|---|
| reel_id | int |
| creator_id | int |
| posted_on | date |
| tags | varchar |
Primary key: reel_id.
One row per reel. tags is lower-case tags without #, separated by single commas with no spaces (at most six), or NULL / '' when the reel has none.
Examples
Example 1
ReelPost
| reel_id | creator_id | posted_on | tags |
|---|---|---|---|
| 1 | 11 | 2025-08-11 | ipl,cricket |
| 2 | 12 | 2025-08-12 | ipl,mumbai,ipl |
| 3 | 13 | 2025-08-13 | cricket,ipl |
| 4 | 11 | 2025-08-14 | monsoon |
| 5 | 14 | 2025-08-05 | ipl |
| 6 | 15 | 2025-08-06 | cricket |
| 7 | 12 | 2025-08-07 | cricket |
| 8 | 13 | 2025-08-15 | cricket,monsoon,foodie |
| 9 | 14 | 2025-08-16 | monsoon |
| 10 | 15 | 2025-08-18 | monsoon,ipl |
| 11 | 16 | 2025-08-17 | NULL |
| 12 | 11 | 2025-08-17 | monsoon |
Output
| tag | this_week | last_week |
|---|---|---|
| monsoon | 4 | 0 |
| ipl | 3 | 1 |
How to solve Meta: Trending Reels Tags Split Out of Comma-Separated Lists
The data is not in first normal form — one column holds a list — so the work is to turn each list into one row per tag, and only then count. The reference peels the list recursively: the anchor takes the first tag with SUBSTRING_INDEX(tags, ',', 1) and keeps the remainder after its comma (SUBSTRING from the tag's length + 2); each recursive step does the same to the remainder until it is empty. NULL and empty lists are filtered out in the anchor, so they produce no tags, and only the two weeks of interest are split at all.
A tag listed twice on one reel must count once, so the rows are reduced to distinct (reel, tag) pairs. Then one grouping by tag counts both weeks with conditional sums, and HAVING applies the trend rule: at least three reels this week and this week ≥ 2 × last week, which a tag absent last week satisfies with 0. The order is fixed by the statement, with the tag name breaking ties.
The alternative splits with a small numbers table instead of recursion over the strings: each reel joins to k = 1 … (commas + 1), and the nested SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', k), ',', -1) picks the k-th element. Both are linear in the total number of tags; a production fix is a separate (reel, tag) table.
Reference solution (MySQL)
WITH RECURSIVE split AS (
SELECT reel_id, posted_on,
SUBSTRING_INDEX(tags, ',', 1) AS tag,
SUBSTRING(tags, CHAR_LENGTH(SUBSTRING_INDEX(tags, ',', 1)) + 2) AS rest
FROM ReelPost
WHERE tags IS NOT NULL AND tags <> ''
AND posted_on BETWEEN '2025-08-04' AND '2025-08-17'
UNION ALL
SELECT reel_id, posted_on,
SUBSTRING_INDEX(rest, ',', 1),
SUBSTRING(rest, CHAR_LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2)
FROM split
WHERE rest <> ''
), tagged AS (
SELECT DISTINCT reel_id, posted_on, tag FROM split
)
SELECT tag,
SUM(CASE WHEN posted_on >= '2025-08-11' THEN 1 ELSE 0 END) AS this_week,
SUM(CASE WHEN posted_on <= '2025-08-10' THEN 1 ELSE 0 END) AS last_week
FROM tagged
GROUP BY tag
HAVING SUM(CASE WHEN posted_on >= '2025-08-11' THEN 1 ELSE 0 END) >= 3
AND SUM(CASE WHEN posted_on >= '2025-08-11' THEN 1 ELSE 0 END) >= 2 * SUM(CASE WHEN posted_on <= '2025-08-10' THEN 1 ELSE 0 END)
ORDER BY this_week DESC, tagAnother way
WITH RECURSIVE n AS (
SELECT 1 AS k UNION ALL SELECT k + 1 FROM n WHERE k < 6
), tagged AS (
SELECT DISTINCT r.reel_id, r.posted_on, SUBSTRING_INDEX(SUBSTRING_INDEX(r.tags, ',', n.k), ',', -1) AS tag
FROM ReelPost r
JOIN n ON n.k <= CHAR_LENGTH(r.tags) - CHAR_LENGTH(REPLACE(r.tags, ',', '')) + 1
WHERE r.tags <> ''
), weekly AS (
SELECT tag,
COUNT(CASE WHEN posted_on BETWEEN '2025-08-11' AND '2025-08-17' THEN 1 END) AS this_week,
COUNT(CASE WHEN posted_on BETWEEN '2025-08-04' AND '2025-08-10' THEN 1 END) AS last_week
FROM tagged GROUP BY tag
)
SELECT tag, this_week, last_week
FROM weekly
WHERE this_week >= 3 AND this_week >= 2 * last_week
ORDER BY this_week DESC, tag← Meta: Growth Accounting of Monthly Active Users · Meta: Each Reels Creator's Percentile Rank Within Their Country →