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 -- TRIM(REPLACE(REPLACE(tag.value, '>', ''), '<', '')) != 'large-language-model' -- AND 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 GROUP BY tag ), TotalPosts AS ( SELECT COUNT(DISTINCT Id) AS total_large_language_model FROM Posts 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) ) 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;