YouTube Trends LakehouseBig Data
2.7 million rows of YouTube trending data across ten countries, answering one question: what should a new channel make?
Raw files from ten countries land in Azure cloud storage, Snowflake reads them in place, and SQL turns them into one clean table of 2.6 million rows. The analysis ends in an actual recommendation: comedy earns the highest engagement of any category, and it works across countries.
Files in, one table out
Trending videos arrive as one CSV per country plus nested JSON category definitions. An external stage lets Snowflake read the files where they sit in Azure Blob Storage; the country code is derived from each file name, and the JSON is flattened in SQL. One subtlety matters: the same category id means different things in different countries, so the join key is composite (country plus category id), and every row receives a generated id, which is what later lets the cleaning delete exact duplicates safely. Everything lands in a single 2,667,041 row table, and ingestion is SQL end to end, with no scripts outside the warehouse.
Cleaning as queries
The same video can trend in the same country on the same day more than once in the raw data, so a window function ranks the duplicates by view count and keeps the highest, the most representative record of that day. Corrupted video ids were deleted, a missing category label was repaired with an update after a cross-country comparison identified what it should be, and the table went from 2,667,041 to 2,597,494 rows with every step verified by a count. Each fix is documented next to its query, so the cleaning reads like code review material, not a mystery.
Reading the numbers like an analyst
Averages alone would have told the wrong story. Film and animation tops average views (2.85 million), but the average is hostage to a few monster hits; on the median, comedy jumps to second (739 thousand, above science and technology's 636), meaning its performance is consistent rather than lottery-shaped. The country cut adds the caution: entertainment dominates almost everywhere, up to 42.35 percent of trending videos in India, except Canada and the United States, where gaming leads. And as a cross-border sanity check, videos mentioning BTS trended 468 times in Korea, 288 in India and 268 in the United States.
The business answer
The brief: which category should a new channel bet on, with music and entertainment excluded, and does the answer hold across countries? Comedy took the highest average like ratio of any category (6.19, ahead of education at 5.42), held the strongest median-to-average consistency, and trended in volume everywhere from India (2,995 distinct videos) to Mexico (512). The recommendation lands as a concrete strategy, quoted from the report: comedy shorts without spoken language, balancing global accessibility with strong engagement.
SELECT
country,
DATE_TRUNC(MONTH, trending_date) AS year_month,
title,
channeltitle,
category_title,
view_count,
TRUNC((likes / view_count) * 100, 2) AS likes_ratio
FROM table_youtube_final
WHERE YEAR(trending_date) = 2024
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY country, DATE_TRUNC('MONTH', trending_date)
ORDER BY view_count DESC
) = 1
ORDER BY year_month, country;