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,60866002,60996324,61243073,61520938,70990386,71006411,71043010,71167600,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,73083132,73326892,73407373,73431551,73456900,73502727,62040659,62083380,62398875,62803219,63589819,73524856,74424083,74596793,63723006,63878853,64037971,64050391,64760396,74886418,74898751,75171118,75205546,75299930,64962547,65078203,65078281,65471612,65481370,65491416,65498670,65527210,65553960,75480058,75549595,75664095,75720004,75721113,65828005,66259690,66265109,66277912,66289113,66304067,66312016,66395599,66546336,66576677,75874823,75918443,76106294,66579541,66581075,66875831,76140070,76178468,76288080,76658088,67051923,67077765,67567802,67570514,67602060,67633859,67634399,67678734,67810671,67842524,67971878,68018178,76750150,76772278,76935633,77107057,77136636,77373303,77377438,77513547,68233576,68269539,77534661,77547784,77732938,77798059,77876217,77918043,78138686,78176551,78185619,78192538,78221905,78271162,78294778,78389817,78398118,78398361,7841072)
),
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,60866002,60996324,61243073,61520938,70990386,71006411,71043010,71167600,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,73083132,73326892,73407373,73431551,73456900,73502727,62040659,62083380,62398875,62803219,63589819,73524856,74424083,74596793,63723006,63878853,64037971,64050391,64760396,74886418,74898751,75171118,75205546,75299930,64962547,65078203,65078281,65471612,65481370,65491416,65498670,65527210,65553960,75480058,75549595,75664095,75720004,75721113,65828005,66259690,66265109,66277912,66289113,66304067,66312016,66395599,66546336,66576677,75874823,75918443,76106294,66579541,66581075,66875831,76140070,76178468,76288080,76658088,67051923,67077765,67567802,67570514,67602060,67633859,67634399,67678734,67810671,67842524,67971878,68018178,76750150,76772278,76935633,77107057,77136636,77373303,77377438,77513547,68233576,68269539,77534661,77547784,77732938,77798059,77876217,77918043,78138686,78176551,78185619,78192538,78221905,78271162,78294778,78389817,78398118,78398361,7841072)
)
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