0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

WITH CleanTags AS (
    SELECT 
        Id, 
        TRIM(
            REPLACE(
                REPLACE(tag.value, '>', ''), 
                '<', 
                ''
            )
        ) AS tag -- Clean extra characters
    FROM 
        Posts CROSS APPLY STRING_SPLIT(
            REPLACE(
                REPLACE(Tags, '><', ','), 
                '<', 
                ''
            ), 
            ','
        ) AS tag 
    WHERE 
        Id IN (
59430106, 59481435, 59484278, 68691256, 69385064, 59622300,59741397, 59835749, 60198252, 60202610, 69499432, 69525476, 69538510, 69596586, 69841983, 69891610, 69965649, 70196914, 70333328, 70338012, 70590588, 70825390, 70990386, 71006411, 71043010, 71167600, 60866002, 60996324, 61243073, 61520938, 71209010, 71285825, 71441870, 71470160, 71496155, 71630891, 71680438, 71687929, 71767784, 71883746, 71885781, 72076723, 72086887, 72121192, 72146421, 72179718, 72263736, 72335489, 72467705, 72556795, 72558094, 72584612, 72652038, 72698170, 72713816, 72777309, 72792770, 72818097, 73024656, 62040659, 62083380, 62398875, 62803219, 63589819, 73083132, 73326892, 73407373, 73431551, 73456900
        )
),
TagCooccurrence AS (
    SELECT 
        tag, 
        COUNT(DISTINCT Id) AS cooccurrence_count
    FROM 
        CleanTags
    WHERE 
        tag != 'abc'
    GROUP BY 
        tag
),
TotalLargeLanguageModel AS (
    SELECT 
        COUNT(DISTINCT Id) AS total_large_language_model_count
    FROM 
        CleanTags
    WHERE 
        tag = 'federated-learning'
),
TotalTags AS (
    SELECT 
        tag, 
        COUNT(DISTINCT Id) AS total_tag_count
    FROM 
        CleanTags
    GROUP BY 
        tag
)
SELECT 
    tc.tag,
    (tc.cooccurrence_count / CAST(tll.total_large_language_model_count AS FLOAT)) AS tag_significance_threshold
FROM 
    TagCooccurrence tc
    JOIN TotalLargeLanguageModel tll ON 1=1
    JOIN TotalTags tt ON tc.tag = tt.tag
WHERE 
    (tc.cooccurrence_count / CAST(tll.total_large_language_model_count AS FLOAT)) >= 0.001 -- Adjust threshold if needed
ORDER BY 
    tag_significance_threshold DESC;

fork query

edited nov 5

created nov 5 anonymous