# Analyzing Concepts with SQL

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](/media/concept-occurrences-over-time-4ccddcdb.png)

### 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](/media/keyframes-scored-and-ranked-by-concept-fa5e8f2c.png)

### 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](/media/concept-occurrences-count-c62c020d.png)

### 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.