Q&A for professional and enthusiast programmers
SELECT PostTypeId, COUNT( * ) TotalPostCount, COUNT( CASE WHEN LastActivityDate >= '20110101' THEN 1 ELSE NULL END ) [2011PostCount], COUNT( CASE WHEN LastActivityDate >= '20120101' THEN 1 ELSE NULL END ) [2012PostCount], COUNT( CASE WHEN LastActivityDate < '20110101' THEN 1 ELSE NULL END ) [Pre2011PostCount] FROM dbo.Posts GROUP BY PostTypeId ORDER BY PostTypeId ASC OPTION( USE HINT( 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' ) ); SELECT PostTypeId, COUNT( DISTINCT Score ) CountScoresPerPostType FROM dbo.Posts GROUP BY PostTypeId ORDER BY PostTypeId ASC OPTION( USE HINT( 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' ) ); SELECT Score, COUNT( * ) CountByScore FROM dbo.Posts WHERE PostTypeId = 1 GROUP BY Score ORDER BY CountByScore DESC, Score DESC OPTION( USE HINT( 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' ) ); SELECT DISTINCT Scores.Score, Scores.CountByScore, CASE WHEN TopFiveThousand > 5000 THEN 1 ELSE 0 END CouldBeArbitrarilyChosen FROM ( SELECT Score, ROW_NUMBER() OVER ( ORDER BY Score DESC ) TopFiveThousand, RANK() OVER ( ORDER BY Score DESC ) PotentiallyAmbiguous, COUNT( * ) OVER ( PARTITION BY Score ) CountByScore FROM dbo.Posts WHERE PostTypeId = 1 ) Scores WHERE Scores.PotentiallyAmbiguous <= 5000 ORDER BY Scores.Score DESC OPTION( USE HINT( 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' ) );