> 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/analyzing-concepts-with-sql/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.coactive.ai/_mcp/server.

Understanding **concept occurrences** in images and videos is essential for **metadata generation**, **content categorization,** and **trend analysis**. This tutorial will guide you through SQL queries that help you:

* **Retrieve concept occurrences over time** to track when a concept appears in videos.
* **Identify images labeled with a specific concept** and evaluate confidence scores.
* **Identify videos labeled with a specific concept and count labeled frames** to measure concept frequency.

By the end of this tutorial, you’ll be able to extract structured insights from concept-based metadata using SQL.

## Retrieve Concept Occurrences Over Time

This query helps pinpoint the exact times when a concept appears in a video. For example, tracking the presence of the "baton" concept in sports footage.

```sql
-- Step 1: Identify occurrences of the specific concept in video keyframes
WITH occurrences AS (
  SELECT
    coactive_video_id,              -- Video ID
    keyframe_time_ms,               -- Timestamp in milliseconds
    baton_prob,                     -- Probability score for the baton concept
    MAX(coactive_image_id) AS coactive_image_id  -- Select the best image for representation
  FROM
    coactive_table_adv
  WHERE
    baton_prob > 0.5  -- Only include keyframes with a confidence score above 0.5
  GROUP BY
    coactive_video_id, keyframe_time_ms, baton_prob
)

-- Step 2: Retrieve and order results for concept occurrences
SELECT
  o.coactive_video_id,              -- Video ID
  o.keyframe_time_ms,               -- Time of occurrence
  o.baton_prob,                     -- Concept probability
  o.coactive_image_id               -- Best image for this occurrence
FROM
  occurrences o
ORDER BY
  o.coactive_video_id, o.keyframe_time_ms ASC;
```

### Explanation

1. **CTE (occurrences)**: Filters frames where the "baton" concept appears with a probability above 0.5. Group data to ensure each timestamp has a representative image.
2. **Final Query**: Retrieves and orders occurrences by video ID and timestamp, creating a timeline of when the concept appears.

![Concept occurrences over time](/_fern-img/4ccddcdb98a699d7fea9d6535431762ef7648dac6815312b701f1426ae23e0c5.webp)

### Use Cases

* Track specific moments when an object or action appears in a video.
* Provides a chronological view of occurrences within each video.
* Applications: Supports video editing, metadata creation, and storytelling based on specific concepts.

## Identify Images Labeled with a Specific Concept

This query retrieves images that can be labeled with a specific concept, along with their confidence scores.

```sql
-- Step 1: Filter images with the specified concept and sufficient confidence
WITH filtered_images AS (
  SELECT
    coactive_image_id,      -- Unique identifier for the image
    baton_prob AS baton_prob -- Confidence score for the concept
  FROM
    coactive_table_adv
  WHERE
    baton_prob > 0.1 -- Include only images with a probability above 0.1
)

-- Step 2: Retrieve the filtered images with their concept and certainty
SELECT
  coactive_image_id, -- Image ID
  baton_prob          -- Confidence score for the detected concept
FROM
  filtered_images
ORDER BY
  baton_prob DESC; -- Order by confidence score in descending order
```

### Explanation

1. **CTE (filtered\_images)**: Filters images that have the specified concept (baton) and a confidence score (baton\_prob) above a configurable threshold (e.g., 0.1).
2. **Final Query**: Retrieves relevant images and sorts them by confidence score.

![Keyframes with concept scores ranked](/_fern-img/fa5e8f2c4b25124ff9a3c547a6a0da6378fbe4c8b881e232f914ff77d93b6f62.webp)

### Use Cases

* Identify high-confidence labels for images.
* Improve metadata tagging and searchability for datasets.

## Identify Videos Labeled with a Specific Concept and Count Labeled Frames

This query identifies which videos contain a specific concept and how many frames within each video are labeled with that concept.

```sql
-- Step 1: Aggregate occurrences of the concept across videos
WITH occurrences AS (
  SELECT
    coactive_video_id,
    COUNT(*) AS occurrence_count,  -- Count the number of frames with the concept
    MAX(coactive_image_id) AS coactive_image_id, -- Select a representative image for the video
    MAX(baton_prob) AS max_probability, -- Highest probability for the concept in the video
    MIN(baton_prob) AS min_probability -- Lowest probability for the concept in the video
  FROM
    coactive_table_adv
  WHERE
    baton_prob > 0.1 -- Only include frames with a probability above this threshold
  GROUP BY
    coactive_video_id -- Group by video to analyze concept occurrences within each
)

-- Step 2: Select relevant metadata for each video
SELECT
  o.max_probability,   -- Maximum probability for the concept
  o.min_probability,   -- Minimum probability for the concept
  o.occurrence_count,  -- Number of frames with the concept
  o.coactive_video_id, -- Video ID
  o.coactive_image_id  -- Best representative image ID for the video
FROM
  occurrences o
ORDER BY
  o.occurrence_count DESC; -- Order by the number of frames with the concept
```

### Explanation

* **CTE (occurrences)**: Aggregates concept occurrences within each video, focusing on frames where the probability of the "baton" concept exceeds 0.1 and records the highest and lowest confidence scores.
* **Final Query**: Orders results by occurrence count to identify videos where the concept appears most frequently.

![Keyframes with concept scores ranked](/_fern-img/c62c020de7514d44f7da6602877ab594453482f3451375720e523c5ecfe8b048.webp)

### Use Cases

* Automate concept-based video labeling.
* Understand concept density in long-form content.
* Improve content moderation and compliance monitoring. Knowing how many frames in a video exhibit the concept helps evaluate its significance within the video.