Work in progress

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

DECLARE @Users     TABLE
    (
    UserId         INT PRIMARY KEY,
    QuestionCount  INT
    )

INSERT INTO
    @Users
    
SELECT
    Users.Id                         AS UserId,
    COUNT( DISTINCT Question.Id )    AS QuestionCount
    
FROM
    Users
    
    INNER JOIN
    Posts                            AS Question
    ON  Users.Id                     = Question.OwnerUserId
    
WHERE
        Question.PostTypeId          = 1 --questions
    AND Users.CreationDate           > '6/1/2010'
    
GROUP BY
    Users.Id
    
HAVING
    COUNT( DISTINCT Question.Id )    > 7 
    


SELECT
    Users.UserId                     AS [User Link],
    Users.QuestionCount              AS [Question Count],
    COUNT( Accepted.Id )
    
FROM
    @Users                           AS Users
    
    INNER JOIN
    Posts                            AS Accepted
    ON  Users.UserId                 = Accepted.OwnerUserId
    
WHERE
        Accepted.PostTypeId          = 1 --questions
    AND Accepted.AcceptedAnswerId    IS NOT NULL  
    
GROUP BY
    Users.UserId,
    Users.QuestionCount
    
HAVING
    COUNT( Accepted.Id )             < 2

    
    
    
  /*  
DECLARE @Unaccepted   TABLE
(
UserId          INT PRIMARY KEY,
AcceptedCount   INT
)

INSERT INTO
    @Accepted
    
SELECT
    Users.UserId                     AS UserId,
    COUNT( DISTINCT Unaccepted.Id )  AS Unaccepted--,
    --COUNT( DISTINCT Accepted.Id )    AS Accepted
    
FROM
    @Users                           AS Users
    
    INNER JOIN
    Posts                            AS Unaccepted
    ON  Users.UserId                 = Unaccepted.OwnerUserId
    AND Unaccepted.PostTypeId        = 1 --questions
    AND Unaccepted.AcceptedAnswerId  IS NULL  
    
    --LEFT JOIN
    --Posts                            AS Accepted
    --ON  Users.Id                     = Accepted.OwnerUserId
    --AND Accepted.PostTypeId          = 1 --questions
    --AND Accepted.AcceptedAnswerId    IS NOT NULL  
    
GROUP BY
    Users.UserId,
    Users.Questions

--HAVING
--        COUNT( DISTINCT Unaccepted.Id )  = Users.Questions
    --AND COUNT( DISTINCT Unaccepted.Id )  = COUNT( DISTINCT Accepted.Id )
    
--OPTION
    --( FORCE ORDER, LOOP JOIN )
    */

Enter Parameters

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