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

In this tutorial we will cover metadata generation for videos.

The goal is to generate a table with one row for each video and one column for each tag indicating if that tag is present or not in the video. Coactive already provides the scores for different dynamic tags at the keyframe level in the table so generating metadata is basically an aggregation of those scores at the video level.

It is possible to create metadata from Dynamic Tags and Concepts.

## Metadata Generation using Dynamic Tags

The SQL query below generates metadata for videos based on visual dynamic tags scores. It follows the rule to tag every video with K or more keyframes with dynamic tag scores above the percentile **P**.

If one wants to do the same exercise using audio dynamic tags scores, the same query can be used. The only necessary adaptation is replacing the tables `category_<dynamic_tag_name>_visual` for the tables `category_<dynamic_tag_name>_audio`.

```sql
WITH 
-- Step 1
dt_count AS (
  SELECT 
    dynamic_tag,
    COUNT(*) AS dt_count
  FROM category_<dynamic_tag_name>_visual
  GROUP BY dynamic_tag
),

-- Step 2
percentile_table AS (
  SELECT
  a.coactive_image_id,
  a.dynamic_tag,
  a.dynamic_tag_score,
  ROW_NUMBER() OVER (PARTITION BY a.dynamic_tag ORDER BY a.dynamic_tag_score DESC)/b.dt_count AS percentile
FROM category_<dynamic_tag_name>_visual AS a
LEFT JOIN dt_count AS b
  ON a.dynamic_tag = b.dynamic_tag
),

-- Step 3
hit_table AS (
  SELECT *,
    1 AS hit
FROM percentile_table
-- Change here the percentile P of keyframes to be selected
WHERE percentile < P
),

-- Step 4
join_table AS (
  SELECT
  a.*,
  COALESCE(b.hit, 0) AS hit
FROM category_<dynamic_tag_name>_visual AS a
LEFT JOIN hit_table AS b
  ON a.coactive_image_id = b.coactive_image_id
  AND a.dynamic_tag = b.dynamic_tag
)

-- Step 5
SELECT
  coactive_video_id,
  dynamic_tag,
-- Change here the requeired number of keyframes K above the treshold to tag a video
  CASE WHEN SUM(hit) >= K THEN 1 ELSE 0 END AS num_hits
FROM join_table
GROUP BY 
  coactive_video_id,
  dynamic_tag
```

### Explanation

1. **CTE (dt\_count)**: Count the total number of key frames for each dynamic tag.
2. **CTE (percentile\_table)**: Normalize the dynamic tags scores so that they are between 0 and 1 using the scores distribution for each tag.
3. **CTE (hit\_table)**: Check which keyframes are above a desired threshold P (between 0 and 1). A P of 0.01 means that only the top 1% keyframes of a dynamic tag will be tagged.
4. **CTE (join\_table)**: Join the table with the tags back to the original keyframe table.
5. **Final Query**: Aggregate the table at the video level tagging all the videos with K or more tagged keyframes.

## Metadata Generation using Concepts

The same exercise can also be done with concepts following the SQL query below. It follows the rule to tag videos with **K** or more keyframes with the concept score above a threshold **T**.

```sql
WITH

video_level_tab AS (
SELECT
  COACTIVE_VIDEO_ID,
  SUM(CASE WHEN <concept_name>_prob > T THEN 1 ELSE 0 END) AS NUM_LABELED_KFS,
FROM
  coactive_table_adv
GROUP BY
  COACTIVE_VIDEO_ID
ORDER BY 2 DESC;
)

SELECT
  *,
  CASE WHEN NUM_LABELED_KFS > K THEN 1 ELSE 0 END AS <concept>_tag
FROM
  video_level_tab
GROUP BY
  COACTIVE_VIDEO_ID
ORDER BY 2 DESC;
```

### Explanation

Define a threshold T to tag every keyframe with a concept score above it. The interpretation is that we are tagging every keyframe with a probability T or bigger of belonging to the concept;

1. **CTE (video\_level\_tab)**: count how many keyframes were tagged in a video.
2. **Final Query**: Tag the videos with K or more tagged keyframes