0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for users of Ethereum, the decentralized application platform and smart contract enabled blockchain

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,59622300,59741397,59835749,60198252,60202610,68691256,69385064,60866002,60996324,61243073,61520938,69499432,69525476,69538510,69596586,69841983,69891610,69965649,70196914,70333328,70338012,70590588,70825390,70990386,71006411,71043010,71167600,62040659,62083380,62398875,62803219,63589819,71209010,71285825,71441870,71470160,71496155,71630891,71680438,71687929,71767784,71883746,71885781,72076723,63723006,63878853,64037971,64050391,64760396,72086887,72121192,72146421,72179718,72263736,72335489,72467705,72556795,72558094,72584612,72652038,72698170,72713816,72777309,72792770,72818097,73024656,64962547,65078203,65078281,65471612,65481370,65491416,65498670,65527210,65553960,73083132,73326892,73407373,73431551,73456900,73502727,65828005,66259690,66265109,66277912,66289113,66304067,66312016,66395599,66546336,66576677,66579541,66581075,66875831,73524856,74424083,74596793,67051923,67077765,67567802,67570514,67602060,67633859,67634399,67678734,67810671,67842524,67971878,68018178,74886418,74898751,75171118,75205546,75299930,75480058,75549595,75664095,75720004,75721113,68233576,68269539,75874823,75918443,76106294,76140070,76178468,76288080,76658088,76750150,76772278,76935633,77107057,77136636,77377438,77513547,77534661,77547784,77732938,77798059,77876217,77918043,78138686,78176551,78185619,78192538,78221905,78271162,78294778,78389817,78398118,78398361,78410724,78557504,78567104,78742773,78835380,78848605,78852053,78854316,78860719,78886217,78913655,78975525,79070306
        )
),
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 khairul.alam