DataSocial docs: TikTok tables, SQL tips and the MCP

SELECT creator_id, username, nickname, followers, likes, videos
FROM tiktok.creators
WHERE country = 'US'
ORDER BY followers DESC
LIMIT 10

SELECT country, count() AS creators
FROM tiktok.creators
WHERE followers >= 1000000 AND country != ''
GROUP BY country
ORDER BY creators DESC
LIMIT 15

SELECT title, artist, video_count, video_count_date
FROM tiktok.sounds
WHERE title != ''
ORDER BY video_count DESC
LIMIT 10

SELECT title, owner_username, video_count
FROM tiktok.sounds
WHERE is_original = 1
ORDER BY video_count DESC
LIMIT 10

WITH recent AS (
    SELECT video_id, creator_id, posted_at, views
    FROM tiktok.videos
    WHERE country = 'US' AND posted_at >= now() - INTERVAL 2 DAY
    ORDER BY views DESC
    LIMIT 200
),
usual AS (
    SELECT creator_id, median(views) AS typical_views
    FROM tiktok.videos
    WHERE country = 'US' AND posted_at BETWEEN now() - INTERVAL 90 DAY AND now() - INTERVAL 2 DAY
      AND creator_id IN (SELECT creator_id FROM recent)
    GROUP BY creator_id
    HAVING count() >= 5
)
SELECT r.video_id, r.posted_at, r.views, round(u.typical_views) AS typical_views,
       round(r.views / u.typical_views, 1) AS times_usual_views
FROM recent AS r
JOIN usual AS u USING (creator_id)
WHERE u.typical_views > 0 AND r.views >= 10 * u.typical_views
ORDER BY times_usual_views DESC
LIMIT 10

SELECT video_id, posted_at, views, likes, caption
FROM tiktok.videos
WHERE country = 'US' AND posted_at >= now() - INTERVAL 7 DAY
ORDER BY views DESC
LIMIT 10

SELECT arrayJoin(hashtags) AS hashtag, count() AS videos
FROM tiktok.videos
WHERE country = 'US' AND posted_at >= now() - INTERVAL 7 DAY
GROUP BY hashtag
ORDER BY videos DESC
LIMIT 15

WITH (
    SELECT sound_id FROM tiktok.videos
    WHERE posted_at >= now() - INTERVAL 30 DAY AND sound_id != 0
    GROUP BY sound_id ORDER BY count() DESC LIMIT 1
) AS top_sound
SELECT country, count() AS videos_30d, round(videos_30d / sum(videos_30d) OVER () * 100, 1) AS share_pct
FROM tiktok.videos
WHERE sound_id = top_sound AND posted_at >= now() - INTERVAL 30 DAY
GROUP BY country
ORDER BY videos_30d DESC
LIMIT 10

WITH posters AS (
    SELECT creator_id, count() AS videos_30d
    FROM tiktok.videos
    WHERE country = 'US' AND posted_at >= now() - INTERVAL 30 DAY
    GROUP BY creator_id
)
SELECT c.username, c.followers, round(p.videos_30d / 30 * 7, 1) AS posts_per_week, p.videos_30d
FROM posters AS p
JOIN (
    SELECT creator_id, username, followers FROM tiktok.creators
    WHERE country = 'US' AND followers >= 100000 AND creator_id IN (SELECT creator_id FROM posters)
) AS c USING (creator_id)
ORDER BY posts_per_week DESC
LIMIT 10

WITH ai AS (
    SELECT creator_id, any(country) AS country, count() AS videos_30d, countIf(is_ai_generated = 1) AS ai_videos
    FROM tiktok.videos
    WHERE posted_at >= now() - INTERVAL 30 DAY
    GROUP BY creator_id
    HAVING videos_30d >= 5 AND ai_videos > 0
    ORDER BY ai_videos / videos_30d DESC, ai_videos DESC
    LIMIT 30
)
SELECT c.username, c.followers, ai.videos_30d, ai.ai_videos, round(ai.ai_videos / ai.videos_30d * 100) AS ai_pct
FROM ai
JOIN (
    SELECT creator_id, username, followers FROM tiktok.creators
    WHERE country IN (SELECT country FROM ai) AND creator_id IN (SELECT creator_id FROM ai)
) AS c USING (creator_id)
ORDER BY ai_pct DESC, ai.ai_videos DESC
LIMIT 10

SELECT creator_id, date, followers, videos, likes
FROM tiktok.creator_history
WHERE date = (SELECT max(date) FROM tiktok.creator_history)
ORDER BY followers DESC
LIMIT 10

WITH (
    SELECT video_id FROM tiktok.video_history
    GROUP BY video_id HAVING count() >= 2
    ORDER BY max(views) DESC LIMIT 1
) AS top_video
SELECT date, views, likes
FROM tiktok.video_history
WHERE video_id = top_video
ORDER BY date

WITH (SELECT max(date) FROM tiktok.sound_history) AS d0,
(
    SELECT groupArray(sound_id) FROM (
        SELECT sound_id, toInt64(n.video_count) - w.video_count AS added_7d
        FROM (SELECT sound_id, video_count FROM tiktok.sound_history WHERE date = d0 - 7) AS w
        JOIN (SELECT sound_id, video_count FROM tiktok.sound_history WHERE date = d0 AND video_count >= 100000) AS n
            USING (sound_id)
        ORDER BY added_7d DESC
        LIMIT 30
    )
) AS rising
SELECT s.title, s.artist, h.videos, h.added_7d, h.history
FROM (
    SELECT sound_id,
           anyIf(video_count, date = d0) AS videos,
           toInt64(videos) - anyIf(video_count, date = d0 - 7) AS added_7d,
           arraySort(groupArray((date, video_count))) AS history
    FROM tiktok.sound_history
    WHERE sound_id IN (SELECT arrayJoin(rising)) AND date >= d0 - 30
    GROUP BY sound_id
) AS h
JOIN (SELECT sound_id, title, artist FROM tiktok.sounds WHERE sound_id IN (SELECT arrayJoin(rising)) AND title != '') AS s USING (sound_id)
ORDER BY h.added_7d DESC
LIMIT 10

WITH (SELECT max(date) FROM tiktok.sound_history) AS d0,
(
    SELECT groupArray(sound_id) FROM (
        SELECT sound_id, (toInt64(n.video_count) - w.video_count) / w.video_count AS growth
        FROM (SELECT sound_id, video_count FROM tiktok.sound_history WHERE date = d0 AND video_count >= 10000) AS n
        JOIN (SELECT sound_id, video_count FROM tiktok.sound_history WHERE date = d0 - 7 AND video_count >= 10000) AS w
            USING (sound_id)
        ORDER BY growth DESC
        LIMIT 30
    )
) AS rising
SELECT s.title, s.artist, h.videos_now, h.added_7d, round(h.added_7d / h.videos_week_ago * 100, 1) AS growth_pct_7d
FROM (
    SELECT sound_id,
           anyIf(video_count, date = d0) AS videos_now,
           anyIf(video_count, date = d0 - 7) AS videos_week_ago,
           toInt64(videos_now) - videos_week_ago AS added_7d
    FROM tiktok.sound_history
    WHERE sound_id IN (SELECT arrayJoin(rising)) AND date IN (d0, d0 - 7)
    GROUP BY sound_id
) AS h
JOIN (SELECT sound_id, title, artist FROM tiktok.sounds WHERE sound_id IN (SELECT arrayJoin(rising)) AND title != '') AS s USING (sound_id)
ORDER BY growth_pct_7d DESC
LIMIT 10

WITH (
    SELECT sound_id FROM tiktok.sound_history
    WHERE date = (SELECT max(date) FROM tiktok.sound_history)
    ORDER BY video_count DESC LIMIT 1
) AS top_sound
SELECT date, video_count
FROM tiktok.sound_history
WHERE sound_id = top_sound
ORDER BY date

WITH (SELECT max(date) FROM tiktok.sound_history) AS d0,
(
    SELECT groupArray(sound_id) FROM (
        SELECT sound_id,
               toInt64(any(n.video_count)) - anyIf(o.video_count, o.date = d0 - 7) AS added_this_week,
               toInt64(anyIf(o.video_count, o.date = d0 - 7)) - anyIf(o.video_count, o.date = d0 - 14) AS added_week_before
        FROM (SELECT sound_id, date, video_count FROM tiktok.sound_history WHERE date IN (d0 - 7, d0 - 14)) AS o
        JOIN (SELECT sound_id, video_count FROM tiktok.sound_history WHERE date = d0 AND video_count BETWEEN 10000 AND 100000) AS n
            USING (sound_id)
        GROUP BY sound_id
        HAVING countIf(o.date = d0 - 7) > 0 AND countIf(o.date = d0 - 14) > 0 AND added_week_before > 0
        ORDER BY added_this_week - added_week_before DESC
        LIMIT 30
    )
) AS picked
SELECT s.title, s.artist, h.videos_now, h.added_this_week, h.added_week_before
FROM (
    SELECT sound_id,
           anyIf(video_count, date = d0) AS videos_now,
           toInt64(videos_now) - anyIf(video_count, date = d0 - 7) AS added_this_week,
           toInt64(anyIf(video_count, date = d0 - 7)) - anyIf(video_count, date = d0 - 14) AS added_week_before
    FROM tiktok.sound_history
    WHERE sound_id IN (SELECT arrayJoin(picked)) AND date IN (d0, d0 - 7, d0 - 14)
    GROUP BY sound_id
) AS h
JOIN (SELECT sound_id, title, artist FROM tiktok.sounds WHERE sound_id IN (SELECT arrayJoin(picked)) AND title != '') AS s USING (sound_id)
ORDER BY h.added_this_week - h.added_week_before DESC
LIMIT 10

SELECT follows_username, follows_nickname, follows_followers, position
FROM tiktok.follows(creator_id = 6596805238354558982)
ORDER BY follows_followers DESC
LIMIT 20

SELECT position, follows_username, follows_nickname, follows_followers
FROM tiktok.follows(creator_id = 6531081683746165760)
ORDER BY position
LIMIT 20