0

Please login or register to vote for this query.

(click on this box to dismiss)

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' ) );

fork query

edited jan 9 20

created jan 9 20 Dannnno