0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

SELECT 
    Language,
    COUNT(DISTINCT p.Id) AS NonDuplicateQuestions
FROM 
    Posts p
CROSS APPLY (
    SELECT 'Python' AS Language WHERE p.Body LIKE '%python%' OR p.Title LIKE '%python%'
    UNION ALL
    SELECT 'JavaScript' AS Language WHERE p.Body LIKE '%javascript%' OR p.Title LIKE '%javascript%'
    UNION ALL
    SELECT 'Java' AS Language WHERE p.Body LIKE '%java%' OR p.Title LIKE '%java%'
    UNION ALL
    SELECT 'C#' AS Language WHERE p.Body LIKE '%c#%' OR p.Title LIKE '%c#%'
    UNION ALL
    SELECT 'C++' AS Language WHERE p.Body LIKE '%c++%' OR p.Title LIKE '%c++%'
    UNION ALL
    SELECT 'Ruby' AS Language WHERE p.Body LIKE '%ruby%' OR p.Title LIKE '%ruby%'
    -- Add more languages as needed
) Lang
WHERE 
    p.PostTypeId = 1 -- Only questions
    AND p.ClosedDate IS NULL -- Exclude closed questions
    AND NOT EXISTS (
        SELECT 1
        FROM Posts dup
        WHERE dup.PostTypeId = 1
          AND dup.Id <> p.Id
          AND dup.Title = p.Title
    )
GROUP BY 
    Language
ORDER BY 
    NonDuplicateQuestions DESC;

fork query

created dec 3 jon.doe46458