0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

WITH UserTrustMetrics AS (
    SELECT 
        u.Id as UserId,
        u.Reputation,
        COUNT(DISTINCT p.Id) as Posts,
        AVG(CAST(p.Score as FLOAT)) as AvgPostScore,
        COUNT(DISTINCT CASE WHEN p.AcceptedAnswerId IS NOT NULL THEN p.Id END) as AcceptedAnswers,
        COUNT(DISTINCT b.Id) as Badges,
        MAX(b.Date) as LastBadgeDate
    FROM Users u
    LEFT JOIN Posts p ON u.Id = p.OwnerUserId
    LEFT JOIN Badges b ON u.Id = b.UserId
    GROUP BY u.Id, u.Reputation
)
SELECT 
    CASE 
        WHEN Reputation >= 20000 THEN 'High Trust'
        WHEN Reputation >= 5000 THEN 'Medium Trust'
        ELSE 'New User'
    END as TrustLevel,
    COUNT(DISTINCT UserId) as UserCount,
    AVG(Posts) as AvgPosts,
    AVG(AvgPostScore) as AvgScore,
    AVG(AcceptedAnswers) as AvgAcceptedAnswers,
    AVG(Badges) as AvgBadges
FROM UserTrustMetrics
GROUP BY CASE 
    WHEN Reputation >= 20000 THEN 'High Trust'
    WHEN Reputation >= 5000 THEN 'Medium Trust'
    ELSE 'New User'
END
ORDER BY AVG(Reputation) DESC;

fork query

edited dec 7

created dec 7 anonymous