Meta: Trending Reels Tags Split Out of Comma-Separated Lists

Hard
768.

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. 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

ColumnType
reel_idint
creator_idint
posted_ondate
tagsvarchar

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.

Example

The rows must come back in the order shown.

ReelPost

reel_idcreator_idposted_ontags
1112025-08-11ipl,cricket
2122025-08-12ipl,mumbai,ipl
3132025-08-13cricket,ipl
4112025-08-14monsoon
5142025-08-05ipl
6152025-08-06cricket
7122025-08-07cricket
8132025-08-15cricket,monsoon,foodie
9142025-08-16monsoon
10152025-08-18monsoon,ipl
2 more rows

Output

tagthis_weeklast_week
monsoon40
ipl31
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
reel_idint
creator_idint
posted_ondate
tagsvarchar

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_idcreator_idposted_ontags
1112025-08-11ipl,cricket
2122025-08-12ipl,mumbai,ipl
3132025-08-13cricket,ipl
4112025-08-14monsoon
5142025-08-05ipl
6152025-08-06cricket
7122025-08-07cricket
8132025-08-15cricket,monsoon,foodie
9142025-08-16monsoon
10152025-08-18monsoon,ipl
11162025-08-17NULL
12112025-08-17monsoon

Output

tagthis_weeklast_week
monsoon40
ipl31

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, tag

Another 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 →