Napište SQL, které vypíše takové uživatele, jež nemají v profilu vyplněno pole WebsiteUrl

0

Please login or register to vote for this query.

(click on this box to dismiss)

Genealogy and Family History Meta

Q&A about the site for expert genealogists and people interested in genealogy or family history

WITH UserPostCounts AS (
    SELECT OwnerUserId, COUNT(*) AS PostCount
    FROM Posts
    GROUP BY OwnerUserId
),
UserCategories AS (
    SELECT
        CASE 
            WHEN PostCount = 0 THEN '0'
            WHEN PostCount BETWEEN 1 AND 10 THEN '1-10'
            ELSE '>10'
        END AS PostCategory,
        COUNT(DISTINCT UP.OwnerUserId) AS UserCount
    FROM 
        Users U
    LEFT JOIN 
        UserPostCounts UP ON U.Id = UP.OwnerUserId
    GROUP BY
        CASE 
            WHEN PostCount = 0 THEN '0'
            WHEN PostCount BETWEEN 1 AND 10 THEN '1-10'
            ELSE '>10'
        END
),
TotalUsers AS (
    SELECT COUNT(*) AS TotalCount FROM Users
)
SELECT 
    uc.PostCategory AS Kategorie_postu,
    COALESCE(uc.UserCount, 0) AS Počet_uživatelů,
    ROUND((COALESCE(uc.UserCount, 0) * 100.0) / tu.TotalCount, 2) AS Procento_z_celkoveho_poctu
FROM 
    UserCategories uc
CROSS JOIN 
    TotalUsers tu
ORDER BY
    CASE 
        WHEN uc.PostCategory = '0' THEN 1
        WHEN uc.PostCategory = '1-10' THEN 2
        ELSE 3
    END;

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results