What this code does it explained in detail in the top portion of the query. I have left out part of the list of language tags as I felt it was just noise. You can see the full collection here. You can run this query on SEDE and play with it as you wish.Note that I left plenty of wiggle room for others to modify and aggregate their own data starting from the simple query at the bottom. 1089 rows returned in 5886 msHow could I improve this with regards to readability, performance, etc.?/** * The objective of this query is to gather data related to cross-posts from *   Stack Overflow (SO) to Code Review (CR). A cross-post as defined in this context is *   a question which has first been asked on SO and then a short time later asked *   again on CR (albeit often slightly modified in the way it is titled or phrased). * Querying from 2 or more sites requires cross-database queries, and the following *   2 databases are used here. All relevant tables are in the [dbo] schema. * - Stack Overflow DB: [StackOverflow] * - Code Review DB:    [StackExchange.Codereview] * 2 temporary tables are used in order to compensate for the physsical limitations *   of SEDE which otherwise will often time out before the query is completed. * param @minutesFromSoPostToCrPost int not null : The number of minutes allowed between the original *   SO question and its cross-post on CR. Default 120 minutes. * param @maximumCharacterCountDifferenceAllowed int not null : The maximum number *   of characters difference between the body of the question. *   NOTE: The higher the number, the more likely that it's not actually a cross-post. */if object_id('tempdb..#LanguageTags') is not null    drop table #LanguageTags;if object_id('tempdb..#CrossPosts') is not null    drop table #CrossPosts;gocreate table #LanguageTags (    TagName varchar(35) collate SQL_Latin1_General_CP1_CS_AS    , constraint pk_#LanguageTags primary key (TagName));goinsert into #LanguageTags (TagName)values  ('applescript'),  ('asp.net-mvc-3'),  ('bash'),  ('brainfuck'),  ('c'),  ('c#'),  ('c++'),  /*SNIP...*/  ('sql'),  ('swift'),  ('wolfram-mathematica'),  ('xslt');godeclare @questionPost int = 1;declare @minutesFromSoPostToCrPost int = 120;declare @maximumCharacterCountDifferenceAllowed int = 1000;select    [Primary Stack] = case        when SoUsers.Reputation >= CrUsers.Reputation then             'Stack Overflow'        else             'Code Review'         end  , [Primary User] = case        when SoUsers.Reputation >= CrUsers.Reputation then            'http://stackoverflow.com/users/' + convert(varchar(10), SoUsers.Id) + '|' + SoUsers.DisplayName        else             'http://codereview.stackexchange.com/users/' + convert(varchar(10), CrUsers.Id) + '|' + CrUsers.DisplayName         end        , [SO Original] = 'http://stackoverflow.com/questions/' + convert(varchar(10), SoPosts.Id) + '|' + SoPosts.Title        , [CR Xpost] = 'http://codereview.stackexchange.com/questions/' + convert(varchar(10), CrPosts.Id) + '|' + CrPosts.Title  /*Calculate the character difference of the body of both questions.*/  , [CharCountDiff] = abs(len(CrPosts.Body) - len(SoPosts.Body))  , [SO Score] = SoPosts.Score  , [CR Score] = CrPosts.Score  , [SO Status] = case        when SoPosts.DeletionDate is not null then 'Deleted'        when SoPosts.ClosedDate is not null then 'Closed'        else 'OK' end  , [CR Status] = case        when CrPosts.DeletionDate is not null then 'Deleted'        when CrPosts.ClosedDate is not null then 'Closed'        else 'OK' end  /*Check in @Duga comments*/  , [DugaComments?] = case        when exists (            select 1 from [StackOverflow].dbo.Comments as SoComments            where SoPosts.Id = SoComments.PostId            and SoComments.Text like '%code%review%'        ) then 'True' end  , [SO Answers] = SoPosts.AnswerCount  , [CR Answers] = CrPosts.AnswerCount  , [SO Accept?] = case         when SoPosts.AcceptedAnswerId is not null then 'True' end  , [CR Accept?] = case         when CrPosts.AcceptedAnswerId is not null then 'True' end  , [SO Created] = SoPosts.CreationDate  , [Minutes to Xpost] = datediff(minute, SoPosts.CreationDate, CrPosts.CreationDate)  , [Tags] = CrPosts.Tags/*Adding results into temp table to avoid timeouts in `select distinct`*/into #CrossPostsfrom    /*Common users across CR and SO sites:*/    [StackExchange.Codereview].dbo.Users as CrUsers    inner join [StackOverflow].dbo.Users as SoUsers        /*AccountId is network-wide Id for each user, and          is distinct from the UserId which is for a specific site*/        on  CrUsers.AccountId = SoUsers.AccountId    /*Questions by user on both sites:*/    inner join [StackExchange.Codereview].dbo.Posts as CrPosts        on  CrUsers.Id = CrPosts.OwnerUserId        and CrPosts.PostTypeId = @questionPost    inner join [StackOverflow].dbo.Posts as SoPosts        on  SoUsers.Id = SoPosts.OwnerUserId        and SoPosts.PostTypeId = @questionPost    /*Bring in tags so we can try to eliminate false matches      due to unrelated posts potentially being posted by the same      user on 2 different sites within our scoped time period.*/    inner join [StackExchange.Codereview].dbo.PostTags as CrPT        on CrPosts.Id = CrPT.PostId    inner join [StackExchange.Codereview].dbo.Tags as CrTags        on CrPT.TagId = CrTags.Id    inner join [StackOverflow].dbo.PostTags as SoPT        on SoPosts.Id = SoPT.PostId    inner join [StackOverflow].dbo.Tags as SoTags        on  SoPT.TagId = SoTags.Idwhere     /*Q was first posted on SO, then later on CR*/    SoPosts.CreationDate < CrPosts.CreationDate    /*Q was posted on CR within a certain number of minutes after being posted on SO*/    and datediff(minute, SoPosts.CreationDate, CrPosts.CreationDate) <= @minutesFromSoPostToCrPost    /*Match at least one language tag from CR->SO per post      Note: We use `select distinct` on the query against #CrossPosts        due to SEDE timing out if attempting to do it during this query.*/    and CrTags.TagName = SoTags.TagName    and exists (        select 1 from #LanguageTags as Langs        where CrTags.TagName = Langs.TagName    )    /*Apply filter based on character count difference of the body of both questions.*/    and abs(len(CrPosts.Body) - len(SoPosts.Body)) <= @maximumCharacterCountDifferenceAllowed;/*Use this query to view full result set, or modify it   according to your needs to aggregate from the #CrossPosts table.*/select distinct *from #CrossPostsorder by [SO Created] desc;
Cross-posts from Stack Overflow to CR
sql;sql server;stackexchange
StyleI like your style.  I don't mind reading keywords in lowercase.  Ithink it's easier on the eyes, but then again, I'm fairly used tocase-sensitive languages with lowercase keywords and syntaxhighlighting.I'm also very fond of the prefix column aliases as opposed to thepostfix AS Alias style that is mainstream.Bug if I would have to nitpick: the hanging comma in the create tablestatement messes with the alignment of the column names.  There :)Filter-join on lookup tableThe joins on both Tags tables is very deeply nested.  Which means,that for each and every Post, SQL will join with each and everyPostTag (which is unavoidable, because they are needed in anunfortunate m-to-n match-and-filter), and then for each of those, itwill join with the Tags table.The Tags table is actually not needed in this join.  We canpre-lookup all the tags that we want to use, by adding theirrespective TagId values to the #LanguageTags temp table asfollows:create table #LanguageTags (    TagName varchar(35) collate SQL_Latin1_General_CP1_CS_AS    , CrTagId int    , SoTagId int    , constraint pk_#LanguageTags primary key (TagName));goinsert into #LanguageTags (TagName)values  ('applescript'),  ('asp.net-mvc-3'),  ('bash'),  ('brainfuck'),  ('c'),  ('c#'),  ('c++'),  /*SNIP...*/  ('sql'),  ('swift'),  ('wolfram-mathematica'),  ('xslt');goupdate Langs   set CrTagId = CrTags.Id     , SoTagId = SoTags.Id  from #LanguageTags Langs inner join [StackExchange.CodeReview].dbo.Tags as CrTags         on CrTags.TagName = Langs.TagName inner join [StackOverflow].dbo.Tags as SoTags         on SoTags.TagName = Langs.TagName;Now we can use this table to filter tags that we want to see byTagId, and we can also join the posts from both sites to a singletable now.SEDE and cross database queriesIt seems that SEDE is not really optimized to join the large databasetables to temp tables.  So when we put the join described above insidethe fetching query, we get a timeout.  The solution: store the TagIdvalues in the temp table:select  -- SNIP  , [Tags] = CrPosts.Tags  , [CrTagId] = CrPT.TagId  , [SoTagId] = SoPT.TagIdand then filter-and-distinct on the #LanguageTags table in the finalquery:select distinct       CP.[Primary Stack]     , CP.[Primary User]     , CP.[SO Original]     , CP.[CR Xpost]     , CP.[CharCountDiff]     , CP.[SO Score]     , CP.[CR Score]     , CP.[SO Status]     , CP.[CR Status]     , CP.[DugaComments?]     , CP.[SO Answers]     , CP.[CR Answers]     , CP.[SO Accept?]     , CP.[CR Accept?]     , CP.[SO Created]     , CP.[Minutes to Xpost]     , CP.[Tags]     , CP.[CrTagId]     , CP.[SoTagId]  from #CrossPosts CP    /*Match at least one language tag from CR->SO per post.*/ inner join #LanguageTags as Langs         on Langs.SoTagId = CP.SoTagId        and Langs.CrTagId = CP.CrTagId order by [SO Created] desc;Turns out that this is quite a bit faster, as the query now returns1130 rows returned in 4166 ms