Guides

SQL tips

The few things worth knowing to change a query: names, fast filters, dates, ids and the metrics.

Queries are ClickHouse SQL. If you have written any SQL, you already know most of it.

The basics

Every table is in the tiktok database: write tiktok.creators, tiktok.sounds and so on.
End with a LIMIT. Results stop at 1,000 rows without an account, 10,000 signed in.
Tables are already clean: one row per thing, no duplicates. You never need FINAL or DISTINCT to fix them.

SELECT username, followers
FROM tiktok.creators
WHERE country = 'GB'
ORDER BY followers DESC
LIMIT 20

Fast, cheap filters

Each table is stored sorted by a few columns. A filter on the first of them skips most of the table, so it is faster and cheaper. Each table's page lists them under “Fastest filters”. The big ones:

Table	Sorted by	So filter on
`creators`	country, followers	country, then followers
`videos`	country, posted_at	country, then a date range
`sounds`	sound_id	a list of sound ids
`sound_history`	sound_id, date	sound id, then a date range

For example, the biggest US creators read about 60 MB (3 credits), while the same question with no country reads every creator's followers.

Dates, ids and units

Ids (creators, videos, sounds) are 64-bit numbers, too big for JavaScript. The API sends them as strings; keep them as text in spreadsheets.
Recent rows: WHERE posted_at >= now() - INTERVAL 7 DAY. A day: toDate(posted_at).
Video length is in milliseconds (videos.duration_ms); sound length is in seconds (sounds.duration_s).
Flags are 0 or 1, e.g. is_verified = 1.

Handy functions

You want	Write
How many	`count()`
How many that match	`countIf(followers >= 1000000)`
One row per hashtag of a video	`arrayJoin(hashtags)`
The value on the latest date	`argMax(video_count, date)`
The newest date in a table	`(SELECT max(date) FROM tiktok.sound_history)`

History in one cell: groupArray

groupArray turns many rows into one array, so each sound (or creator) gets one row with its whole history. The results table draws an array of numbers as a mini chart: green if the last value is above the first, red if below. Add the date as a pair, groupArray((date, video_count)), and hovering the chart shows the dates too. Switch the table to Classic to see the exact numbers instead.

SELECT sound_id, groupArray(video_count) AS history
FROM (
    SELECT sound_id, date, video_count
    FROM tiktok.sound_history
    WHERE sound_id IN (7171140178143266818, 7673034501618567944)
      AND date >= today() - 30
    ORDER BY date
)
GROUP BY sound_id

Sort inside the subquery (ORDER BY date) so the array runs oldest to newest. For daily change instead of totals, wrap it: arrayDifference(groupArray(video_count)).

How metrics are defined

Growth % = change ÷ the previous value, never the current one. Put a floor on the previous value (e.g. 10,000 videos) so tiny sounds don't top the list.
Compare two days by reading both: WHERE date IN (d0, d0 - 7), then anyIf(video_count, date = d0 - 7) for the old value, and keep only rows that have both days (HAVING countIf(date = d0 - 7) > 0). The rising-sounds questions on the sound_history page show the whole query.
A missing day is NULL, never zero, and never filled in.
Engagement rate = (likes + comments + shares + saves) ÷ views.
Country spread of a sound, counted from videos, is an estimate from the videos we hold, which lean towards the US.

What isn't allowed

Only reading: no INSERT, CREATE or anything that changes data.
Only the tiktok database, and no SET, SETTINGS or FORMAT: results always come back as a table (JSON over the API).
creators.email can't be read with SQL, so SELECT * on creators is refused: name the columns you want. To get emails, keep creator_id in the result and use Reveal emails (see Credits).