crap.sql

CREATE INDEX 
    igno
ON dbo.Posts 
    (OwnerUserId, PostTypeId)
WHERE 
    (PostTypeId = 1)
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE INDEX 
    rant
ON dbo.Votes 
    (VoteTypeId, UserId, PostId)
INCLUDE 
    (BountyAmount, CreationDate) 
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
 
CREATE INDEX 
    clown 
ON dbo.Badges
    (UserId) 
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO 

CREATE OR ALTER PROCEDURE 
   dbo.VoteSniffing
(
    @VoteTypeId int 
)
AS
SET XACT_ABORT, NOCOUNT ON;

    BEGIN
    
    SET STATISTICS XML ON;

        SELECT   
            UserId = 
                ISNULL(v.UserId, 0),
            Votes2013 = 
                SUM
                (
                    CASE 
                        WHEN (v.CreationDate >= '20130101'
                              AND v.CreationDate < '20140101')
                        THEN 1
                        ELSE 0
                    END
                ),
            TotalBounty = 
                SUM
                (
                    CASE 
                        WHEN v.BountyAmount IS NULL
                        THEN 0
                        ELSE 1
                    END
                ),
            PostCount = 
                COUNT(DISTINCT v.PostId),
            VoteTypeId = 
                @VoteTypeId
        FROM     dbo.Votes AS v
        WHERE    v.VoteTypeId = @VoteTypeId
        AND      NOT EXISTS
                 (   
                     SELECT 
                         1/0
                     FROM dbo.Posts AS p
                     JOIN dbo.Badges AS b 
                         ON b.UserId = p.OwnerUserId 
                     WHERE  p.OwnerUserId = v.UserId
                     AND    p.PostTypeId = 1 
                 )
        GROUP BY v.UserId;

    SET STATISTICS XML OFF;

    END;
GO