> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.coactive.ai/latest/docs-guides/deep-dives/query-your-dataset-with-sql/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.coactive.ai/_mcp/server.

# Query Your Dataset with SQL

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.

> **Tip**
>
> New to SQL on Coactive? Start with [Query Engine](/docs-guides/core-features/query-engine), which covers `SHOW TABLES` and `DESCRIBE`.

> **Note**
>
> 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

```sql
-- 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

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

### Image count

```sql
-- 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

> **Info**
>
> For live ingestion status and error codes, use [Ingestion Observability](/docs-guides/administration/ingestion-observability-beta): 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

```sql
-- 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

```sql
-- 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

```sql
-- 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](/api-reference/api-reference/context-studio/ad-segments/create-ad-segments), 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

```sql
-- 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';
```