Analyzing Dynamic Tags with SQL

1
-- Step 1: Rank dynamic tags for each image based on their scores
2
WITH ranked AS (
3
    SELECT
4
        coactive_video_id,        -- The ID of the video the image belongs to (if applicable)
5
        coactive_image_id,        -- The unique ID for each image
6
        dynamic_tag,              -- The tag assigned to the image
7
        dynamic_tag_score,        -- The confidence score for the tag
8
        ROW_NUMBER() OVER (
9
            PARTITION BY coactive_image_id  -- Separate ranking for each image
10
            ORDER BY dynamic_tag_score DESC  -- Rank tags by confidence score in descending order
11
        ) AS rn                   -- Assign a rank to each tag per image
12
    FROM 
13
        category_sports_visual    -- Source table containing image-level dynamic tags
14
),
15
16
-- Step 2: Filter to keep the most relevant tags for each image
17
image_tag_table AS (
18
    SELECT
19
        coactive_image_id,        -- Image ID
20
        coactive_video_id,        -- Video ID (if applicable)
21
        dynamic_tag,              -- Tag name
22
        dynamic_tag_score         -- Confidence score for the tag
23
    FROM 
24
        ranked
25
    WHERE 
26
        rn <= 3                   -- Keep only the top 3 tags per image
27
        AND dynamic_tag_score > 0.01  -- Exclude low-confidence tags (score <= 0.01)
28
)
29
30
-- Step 3: Aggregate tag distribution across the dataset
31
SELECT 
32
    dynamic_tag,                 -- Tag name
33
    COUNT(DISTINCT coactive_image_id) AS image_count  -- Count the number of unique images with this tag
34
FROM 
35
    image_tag_table
36
GROUP BY 
37
    dynamic_tag                  -- Group results by tag name
38
ORDER BY 
39
    image_count DESC;            -- Order by the frequency of each tag in descending order

1
-- Step 1: Identify all images from datasets with both videos and images
2
WITH video_only AS (
3
    SELECT DISTINCT coactive_image_id
4
    FROM category_sports_visual
5
    WHERE coactive_shot_id IS NOT NULL AND coactive_shot_id != '' -- Filter for valid video data
6
),
7
8
-- Step 2: Rank tags for each image, excluding those associated with videos
9
ranked AS (
10
    SELECT
11
        tags.coactive_image_id,
12
        dynamic_tag,
13
        dynamic_tag_score,
14
        ROW_NUMBER() OVER (
15
            PARTITION BY coactive_image_id -- Rank tags for each image
16
            ORDER BY dynamic_tag_score DESC -- Highest scoring tags first
17
        ) AS rn
18
    FROM category_sports_visual AS tags
19
    WHERE tags.coactive_image_id 
20
NOT IN (SELECT coactive_image_id FROM video_only) -- Exclude video-related images
21
),
22
23
-- Step 3: Filter to retain the top 5 tags per image
24
image_tag_table AS (
25
    SELECT
26
        coactive_image_id,
27
        dynamic_tag,
28
        dynamic_tag_score
29
    FROM ranked
30
    WHERE rn <= 5 -- Keep only the top 5 tags per image
31
          AND dynamic_tag_score > 0.001 -- Exclude low-confidence tags
32
)
33
34
-- Final Output: Retrieve the most significant tags for each image
35
SELECT 
36
    coactive_image_id,
37
    dynamic_tag,
38
    dynamic_tag_score
39
FROM image_tag_table;

1
-- Step 1: Filter for video-specific data only
2
WITH video_only AS (
3
    SELECT * 
4
    FROM category_sports_visual
5
    WHERE coactive_shot_id IS NOT NULL AND coactive_shot_id != '' -- Ensure valid video data
6
),
7
8
-- Step 2: Rank tags for each video and image by score
9
ranked AS (
10
    SELECT
11
        coactive_video_id,
12
        coactive_image_id,
13
        dynamic_tag,
14
        dynamic_tag_score,
15
        ROW_NUMBER() OVER (
16
            PARTITION BY coactive_video_id, coactive_image_id -- Rank tags separately for each image within a video
17
            ORDER BY dynamic_tag_score DESC -- Highest scoring tags first
18
        ) AS rn
19
    FROM video_only
20
),
21
22
-- Step 3: Remove duplicates for tags within each video
23
distinct_tags AS (
24
    SELECT
25
        coactive_video_id,
26
        coactive_image_id,
27
        dynamic_tag,
28
        dynamic_tag_score,
29
        ROW_NUMBER() OVER (
30
            PARTITION BY coactive_video_id, dynamic_tag -- Rank each tag within the same video
31
            ORDER BY dynamic_tag_score DESC -- Keep the highest score for each tag
32
        ) AS tag_rank
33
    FROM ranked
34
    WHERE rn <= 5 -- Keep only the top 5 tags per image
35
          AND dynamic_tag_score > 0.001 -- Exclude low-confidence tags
36
),
37
38
-- Step 4: Rank tags across the video to find the most significant ones
39
video_tag_table AS (
40
    SELECT
41
        coactive_video_id,
42
        coactive_image_id,
43
        dynamic_tag,
44
        dynamic_tag_score,
45
        DENSE_RANK() OVER (
46
            PARTITION BY coactive_video_id -- Rank tags globally for the video
47
            ORDER BY dynamic_tag_score DESC
48
        ) AS rn_video
49
    FROM distinct_tags
50
    WHERE tag_rank = 1 -- Include only the best instance of each tag
51
),
52
53
-- Step 5: Select the top 5 tags for each video
54
video_top_tag_table AS (
55
    SELECT * 
56
    FROM video_tag_table
57
    WHERE rn_video <= 5 -- Limit to the top 5 tags per video
58
    ORDER BY coactive_video_id, dynamic_tag_score DESC
59
)
60
61
-- Final Output: Retrieve the most significant tags for each video
62
SELECT * 
63
FROM video_top_tag_table;