Over on EL&U Meta, someone raised the question of how many net migrations there are to/from our sister site, ELL.
The following SEDE query calculates the net migrations by quarter (
elu2ell - ell2elu). You can play with the query in SEDE here.
-- migration prefix length DECLARE @mhpl INT, @mapl INT, @mhid INT, @maid INT; 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 SELECT * INTO #ph FROM posthistory WHERE PostHistoryTypeId in (@mhid, @maid) -- Add "migration direction" column ALTER TABLE #ph ADD mDir CHAR(1) NULL; UPDATE #ph SET mDir = '<' WHERE PostHistoryTypeId=@mhid UPDATE #ph SET mDir = '>' WHERE PostHistoryTypeId=@maid -- Add column for site where question was migrated from ALTER TABLE #ph ADD mSite varchar(255) NULL; -- Parse migration source site from comment field -- and put into column SET @mhpl = LEN(N'from http://') SET @mapl = LEN(N'to http://' ) UPDATE #ph SET mSite = replace( CASE WHEN mDir = N'<' THEN substring( COMMENT, @mhpl, charindex('.',COMMENT,@mhpl) - @mhpl ) ELSE substring( COMMENT, @mapl, charindex('.',COMMENT,@mapl) - @mapl ) END, N'/', '' ) -- Add calendar quarter column column for site where question was migrated from ALTER TABLE #ph ADD CalendarQuarter CHAR( 7 ) NULL; -- LEN(N'FYXXQYY') UPDATE #ph SET CalendarQuarter = 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) ) 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 NetDepatures FROM #ph WHERE mSite='ell' GROUP BY CalendarQuarter ORDER BY CalendarQuarter DESC
I've used SQL here and there in my professional career, but I'm far from an expert. I'd like commentary on the overall query, structure, organization, style. Particular areas of concern:
- The duplication of the
charindex('.',COMMENT,@mhpl) - @mhpllogic in the migration-site parsing
UPDATEstatement bugs me,
- as does the general verbosity and complexity of the
- You'll also see I use vertical indentation for most deeply-nested function calls. Is this normal or useful practice for complicated SQL queries? If not, how do you help the reader visually parse deeply-nested parens, without wasting so much real estate?
Beyond that, performance optimizations are nice, but it's a small enough dataset that it doesn't matter that much.
A final issue is I worry that the way I've structured the temp table will limit more general analysis to other (non-ELL) sites and over different time horizons, in particular by adding an explicit
I'd like to do that dynamically for the sake of the final query (where I do want quarterly figures), but I found it difficult to both dynamically calculate that column as well
GROUP BY it.
So, if there's a way to structure the final table that makes future, more general analytics easier, that would be useful feedback.
I am also suspicious of the PostHistory table and whether I can rely on
PostHistoryTypeId in [35, 36] to reliably tell me that a question was migrated. But this is CR, not SO, so let's assume the logic is correct, and focus on the code as code.
In re: future extensions, I am aware that meta sites in general will not be categorized properly, as they all collapse down to
meta before Great HTTPs Migration, and are counted among the normal site migrations after it (i.e.
english.stackexchange both map to
english now). But meta-migrations are rare enough for my purposes that I can ignore them. Nor am I concerned with hypothetical migrations to non-
*.stackexchange.com sites (like the original trilogy). That won't happen for EL&U.