Query Your Dataset with SQL | Coactive AI

Every Coactive dataset comes with SQL tables you can query to understand what’s in it. Use the queries on this page to check your dataset’s size, confirm a video was ingested, and see which videos have ad segments.

To run a query, open Queries in the app, select your dataset, paste the query, and run it.

New to SQL on Coactive? Start with Query Engine, which covers SHOW TABLES and DESCRIBE.

Available tables can vary by dataset, so if a query returns TABLE_OR_VIEW_NOT_FOUND, run SHOW TABLES to see what yours has.

Table	What it holds
`coactive_table_video`	One row per video in the dataset, with its `coactive_video_id`, `path`, `created_dt`, and `metadata`
`coactive_table`	One row per image. In video datasets, one row per keyframe
`coactive_table_composite`	Segments of a video, such as ad segments, keyed to the video by `video_id`

Get your dataset size

Video count and total minutes

-- Returns the number of processed videos and approximate total video minutes in the dataset
-- Video duration is estimated using the timestamp of each video's last keyframe
SELECT
    COUNT(*) AS video_count,
    ROUND(SUM(duration_ms) / 60000.0, 1) AS total_video_minutes
FROM (
    SELECT
        v.coactive_video_id,
        MAX(k.keyframe_time_ms) AS duration_ms
    FROM coactive_table_video v
    INNER JOIN coactive_table k
        ON v.coactive_video_id = k.coactive_video_id
    GROUP BY v.coactive_video_id
) t;

Why minutes are approximate

The SQL tables don’t store each video’s total runtime, so this query estimates it from the timestamp of the video’s last keyframe. A keyframe’s timestamp marks where that moment starts, so any footage after the last keyframe isn’t counted, and totals may run slightly under the runtime.

Video count only

-- Returns the total number of videos in the dataset
SELECT COUNT(DISTINCT coactive_video_id) AS video_count
FROM coactive_table_video;

Image count

-- Returns the total number of images in the dataset
-- Use for image datasets only
SELECT COUNT(DISTINCT coactive_image_id) AS image_count
FROM coactive_table;

Check whether a video was ingested

For live ingestion status and error codes, use Ingestion Observability: open your dataset and click View ingestions.

Use SQL to check specific files by path, or to look back further than the rolling 30 days of status Ingestion Observability keeps.

Look up videos by file path

-- Returns a row for each path that exists in the dataset
-- Any path not returned was not ingested
SELECT
    coactive_video_id,
    path,
    created_dt,
    metadata
FROM coactive_table_video
WHERE path IN (
    's3://your-bucket/path/video-1.mp4',
    's3://your-bucket/path/video-2.mp4'
    -- add more paths here
);

See your most recently ingested videos

-- Returns the 50 most recently added videos
SELECT coactive_video_id, path, created_dt
FROM coactive_table_video
ORDER BY created_dt DESC
LIMIT 50;

Count videos ingested in a date range

-- Returns the number of videos added to the dataset between two dates
-- Replace the dates with your range. The end date is not included,
-- so this example covers all of 2025
SELECT COUNT(DISTINCT coactive_video_id) AS video_count
FROM coactive_table_video
WHERE created_dt >= '2025-01-01'
  AND created_dt < '2026-01-01';

Find ad segments

Ad segments are used by segment-level packages in Context Studio. You add them with the Create ad segments API, and they’re stored in coactive_table_composite as rows where type = 'ad-segment'. Segment-level packages skip videos that have no ad segments.

Find videos missing ad segments

-- Returns every video in the dataset that has no ad segments
-- An empty result means every video has ad segments
SELECT v.coactive_video_id, v.path, v.metadata
FROM coactive_table_video v
LEFT ANTI JOIN coactive_table_composite c
    ON c.video_id = v.coactive_video_id
    AND c.type = 'ad-segment';