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
       -- TRIM(REPLACE(REPLACE(tag.value, '>', ''), '<', '')) != 'large-language-model'
       -- AND 
        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
    GROUP BY
        tag
),
TotalPosts AS (
    SELECT
        COUNT(DISTINCT Id) AS total_large_language_model
    FROM
        Posts
    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, 734569000)
)
SELECT
    tc.tag,
    tc.cooccurrence_count,
    (tc.cooccurrence_count / CAST(tp.total_large_language_model AS FLOAT)) AS relevance_ratio
FROM
    TagCooccurrence tc
JOIN
    TotalPosts tp
ON
    1 = 1  -- Cartesian join to include total count in every row
WHERE
    (tc.cooccurrence_count / CAST(tp.total_large_language_model AS FLOAT)) >= .001  -- Adjust threshold if needed
ORDER BY
    relevance_ratio DESC;

fork query

edited nov 5

created nov 5 anonymous