0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for teachers and students of the Esperanto language

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 (
68673736,68691256,59430106,59448706,59622300,59741397,59835749,60070016,60077515,60153603,60181180,60183787,68891143,68926524,69101385,69204874,69281046,69325272,69335892,69385064,60194646,60198252,60265798,60279026,60285187,60285568,60457040,69464111,69499432,69525476,69538510,69542184,69591102,69596586,69607949,60463530,60489703,60501245,60508005,60564780,60757397,60771007,60815004,60839809,60841235,60876340,60903579,60920524,60935065,60966874,60982530,60998080,61008774,61025323,61034455,61054659,61056770,61102268,61108381,69614134,69619028,69739007,69767043,69783251,61136411,61152605,61199743,61219479,69891610,69949143,70160910,70196914,61243073,61268081,61393106,61393382,61419057,61450093,61458419,61603476,70333328,70338012,70376178,70398702,70426568,70434265,70563080,70590588,61677696,61786305,61882422,62040659,70715524,70745268,70825390,71037598,62063550,62092301,62273164,62298296,62311087,62323656,62332459,71160311,71167600,71179882,71231671,71251685,71273332,71285825,71289273,71322383,71330639,71396780,71426658,62398225,62398875,62416384,62544709,62578714,62598087,62657818,62694286,71428904,71441616,71441870,71470160,71496155,62711362,62754913,62786889,62832704,62840937,62893523,71506975,71607216,71630891,71672074,71680438,71710530,62906596,62993389,63043501,63090535,63201833,71748346,71822452,71839866,71883746,71898178,71899247,71988768,72022654,72035825,72052364,72059732,63229611,63456076,63456963,63460420,72076723,72086887,72121192,72132691,72146421,72179718,72230991,72241747,72263736,72327186,63498907,63539599,63577775,63723518,72335489,72384343,72428249,72428317,72467705,72484653,72495342,72501778,72514759,72514941,63809453,63917893,63955527,72556795,72561857,72617036,72652038,63990564,64046242,72674232,72698170,72713816,72734353,72777309,72792770,72818097,72874108,72876080,64053996,64183256,64196780,64280312,64331370,72887912,72899013,72915271,73007445,73024656,73032606,73041436,73053135,73068175,73112324,73184351,73198156,73199007,73303926,64367417,64374989,64398484,64698402,64698895,64714113,64750708,64760396,73326892,73346020,73407373,64791796,64959332,64962547,64970504,73502727,73655593,73671061,73720935,73734072,65071335,65178310,65273151,65274580,65411118,65434193,65447819,65458032,65471612,65481370,65491416,65498670,65553960,65578020,65578498,65637227,65772509,65794925,65828005,65830370,65871666,65935511,65943395,65987943,66042534,66073393,66088323,66182614,66206118,66223590,66259690,66265109,66277072,66277912,66288387,66304067,66309222,66331850,66354417,66395599,66413948,66451298,66472157,73776503,73845079,73889434,74031314,74413904,74572371,74624325,74635696,66546336,66557738,66579541,66581075,66705900,66776030,66778069,67053731,67057583,67077765,67077757,74830029,74886418,74985672,67101197,67138338,67147951,67213520,67384361,67533039,67567802,75171118,75205546,75409716,75614269,67570514,67602060,67633859,67634399,67810671,75636817,75800724,75874823,67971878,67982276,76106294,76132039,68018178,68104098,68269539,68341156,68380729,68405118,68412517,68424157,68436038,68572849,68668322,76192353,76232799,76407619,76450661,76716812,76750150,76827472,77116019,77244883
        )
),
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