SEDE query to calculate net migrations quarterly from ELU to ELL

1

Please login or register to vote for this query.

(click on this box to dismiss)

https://codereview.stackexchange.com/questions/206232/sede-query-to-calculate-net-migrations-quarterly-from-elu-to-ell

Physics

Q&A for active researchers, academics and students of physics

-- migration prefix length
DECLARE @mhpl INT, @mapl INT, @mhid INT, @maid INT;

SET @mhpl = LEN(N'from http://')
SET @mapl = LEN(N'to http://'  )

SELECT @mhid = Id from PostHistoryTypes where Name = N'Post Migrated Here'
SELECT @maid = Id from PostHistoryTypes where Name = N'Post Migrated Away'

-- Create copy of PostHistory table with
-- all the posts migrated here from other sites
;WITH ph as (
  SELECT *
        -- Add "migration direction" column 
       , (CASE PostHistoryTypeId 
          WHEN @mhid THEN '<'
          WHEN @maid THEN '>'
          END
         ) mDir
         -- Add column for site where question was migrated from
         -- Parse migration source site from comment field
         -- and put into column
       , replace( 
            CASE WHEN PostHistoryTypeId = @mhid THEN 
              substring(
                         COMMENT, @mhpl, 
                         charindex('.',COMMENT,@mhpl) - @mhpl
                       )
            ELSE 
              substring(
                         COMMENT, @mapl, 
                         charindex('.',COMMENT,@mapl) - @mapl
                       )
            END,
            N'/', ''
          ) mSite
       -- Add calendar quarter column column for site where question was migrated from
      , N'FY' + 
         RIGHT(
                CAST( YEAR(CreationDate) AS CHAR(4) ), 
                2 
         ) +
         N'Q' + 
         CAST( CEILING(
                       CAST(
                            MONTH(CreationDate) AS DECIMAL(4,2)
                            ) / 3 
                       ) AS char(1)
         ) CalendarQuarter
  FROM posthistory 
  WHERE PostHistoryTypeId in (@mhid, @maid)
)


SELECT 
  CalendarQuarter, 
  SUM(CASE WHEN mDir='>' THEN 1 ELSE  0 END) as Departing,
  SUM(CASE WHEN mDir='<' THEN 1 ELSE  0 END) as Arriving,
  SUM(CASE WHEN mDir='>' THEN 1 ELSE -1 END) as NetDepartures
FROM ph
WHERE mSite='ell'
GROUP BY CalendarQuarter
ORDER BY CalendarQuarter DESC

GO
--- part II
DECLARE @http NVARCHAR(5), @httplen INT, @mhid INT, @maid INT;

SET @http = '%://%';
SET @httplen = LEN(@http) - 2; -- don't count the %

SELECT @mhid = Id from PostHistoryTypes where Name = N'Post Migrated Here'
SELECT @maid = Id from PostHistoryTypes where Name = N'Post Migrated Away'

;WITH ph as (
  SELECT *
       , (
          CASE PostHistoryTypeId 
          WHEN @mhid THEN '<'
          WHEN @maid THEN '>'
          END
         ) mDir
       , SUBSTRING(
           COMMENT, 
           PATINDEX(@http, comment) + @httplen, 
           CHARINDEX('.', COMMENT) - (PATINDEX(@http, comment) + @httplen)
         ) mSite
       , DATEPART(yy, CreationDate) % 100 fy
       , DATEPART(qq, CreationDate) q
  FROM posthistory 
  WHERE PostHistoryTypeId in (@mhid, @maid)
)


SELECT 
  CONCAT('FY', fy, 'Q', q) as CalendarQuarter,
  SUM(CASE WHEN mDir='>' THEN 1 ELSE  0 END) as Departing,
  SUM(CASE WHEN mDir='<' THEN 1 ELSE  0 END) as Arriving,
  SUM(CASE WHEN mDir='>' THEN 1 ELSE -1 END) as NetDepartures
FROM ph
WHERE mSite='ell'
GROUP BY fy,
         q
ORDER BY fy DESC,
          q DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results