What a data script is
A data script is a saved SQL workspace on the Data page (/data). Write a query, run it, and the result opens as a table or a chart. Every run is kept in the run history of the script, and a result can be saved as a snapshot.
Queries run on ClickHouse, over a copy of the site data that is kept in sync with the live database. Use ClickHouse SQL: functions such as toDate, toStartOfMonth and count() work, Postgres-only syntax does not. The list of tables you can query is on the Script examples page.
Rules
- One statement per run, starting with SELECT or WITH. Statements that change data are rejected.
- A run returns at most 10,000 rows. Aggregate with GROUP BY or add LIMIT for bigger tables.
- Times are stored in UTC, so toDate(created_at) groups by the UTC day.
- Most columns can be empty (NULL). Use coalesce or IS NOT NULL when you filter or group by them.
- Use the play button next to a statement to run only that statement when a script holds several.
Count only real profiles
Deleted rows are already hidden, but a table still holds soft-deleted profiles, dry run profiles that expire on their own, and profiles made by automated tests. Add this condition to a query over human_profile to leave them out:
WHERE NOT coalesce(soft_delete_flag, false)
AND dry_run_expires_at IS NULL
AND NOT coalesce(is_e2e_profile, false)
AND (created_by_user_id IS NULL
OR created_by_user_id != toUUID('b0b00001-0001-4001-8001-000000000001'))Example queries
Copy a query into a script and run it. The queries that group by a date read well in the chart view.
Look at a table
The first 25 rows of a table, to see its columns. Replace human_profile with any table from the table list.
SELECT *
FROM human_profile
LIMIT 25;Human profiles created per day
The number of new human profiles for each of the last 90 days, newest first.
SELECT toDate(created_at) AS day,
count() AS created_profiles
FROM human_profile
WHERE created_at IS NOT NULL
AND NOT coalesce(soft_delete_flag, false)
AND dry_run_expires_at IS NULL
AND NOT coalesce(is_e2e_profile, false)
AND (created_by_user_id IS NULL
OR created_by_user_id != toUUID('b0b00001-0001-4001-8001-000000000001'))
GROUP BY day
ORDER BY day DESC
LIMIT 90;Human profiles created per month
The number of new human profiles per calendar month, oldest first.
SELECT toStartOfMonth(created_at) AS month,
count() AS created_profiles
FROM human_profile
WHERE created_at IS NOT NULL
AND NOT coalesce(soft_delete_flag, false)
AND dry_run_expires_at IS NULL
AND NOT coalesce(is_e2e_profile, false)
AND (created_by_user_id IS NULL
OR created_by_user_id != toUUID('b0b00001-0001-4001-8001-000000000001'))
GROUP BY month
ORDER BY month;Latest human profiles
The 25 most recently created human profiles with their id, URL name and English title.
SELECT id, id_for_url, en_title, created_at
FROM human_profile
WHERE NOT coalesce(soft_delete_flag, false)
AND dry_run_expires_at IS NULL
AND NOT coalesce(is_e2e_profile, false)
AND (created_by_user_id IS NULL
OR created_by_user_id != toUUID('b0b00001-0001-4001-8001-000000000001'))
ORDER BY created_at DESC
LIMIT 25;Who created the most profiles
The user accounts that created the most human profiles in the last 30 days.
SELECT created_by_user_id,
count() AS created_profiles
FROM human_profile
WHERE created_at >= now() - INTERVAL 30 DAY
AND NOT coalesce(soft_delete_flag, false)
AND dry_run_expires_at IS NULL
AND NOT coalesce(is_e2e_profile, false)
AND (created_by_user_id IS NULL
OR created_by_user_id != toUUID('b0b00001-0001-4001-8001-000000000001'))
GROUP BY created_by_user_id
ORDER BY created_profiles DESC
LIMIT 20;Verified and not verified profiles
How many human profiles are verified as real people and how many are not.
SELECT is_verified,
count() AS profiles
FROM human_profile
WHERE NOT coalesce(soft_delete_flag, false)
AND dry_run_expires_at IS NULL
AND NOT coalesce(is_e2e_profile, false)
AND (created_by_user_id IS NULL
OR created_by_user_id != toUUID('b0b00001-0001-4001-8001-000000000001'))
GROUP BY is_verified;