[rust] questions with most dupes attached

0

Please login or register to vote for this query.

(click on this box to dismiss)

I just forked a query that looked like it works and hacked in the restriction for [rust] questions.

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

--    (1, 'Exact duplicate'),
--    (101, 'Duplicate'),

with Duplicates(Id, Text, OriginalIds) as (
  select
    p.Id as [Id],
    --p.ParentId,
    --p.ClosedDate,
    --ph.Comment as [Reason Id],
    ph.Text as [Text],
    --CHARINDEX('OriginalQuestionIds', ph.Text) + 1,
    --CHARINDEX(']', ph.Text, CHARINDEX('OriginalQuestionIds',ph.Text)),
    SUBSTRING(
      ph.Text,
      CHARINDEX('[', ph.Text, CHARINDEX('OriginalQuestionIds',ph.Text)) + 1,
      CHARINDEX(']', ph.Text, CHARINDEX('OriginalQuestionIds',ph.Text)) - CHARINDEX('[', ph.Text, CHARINDEX('OriginalQuestionIds',ph.Text)) - 1
    ) as [OriginalIds]
  from
    Posts p
    inner join PostHistory ph on p.Id = ph.PostId
    inner join PostHistoryTypes pht on ph.PostHistoryTypeId = pht.Id
    INNER JOIN PostTags ON p.Id = PostTags.PostId
    INNER JOIN Tags ON PostTags.TagId = Tags.id
  where
    Tags.tagname = 'rust'
    AND p.ClosedDate IS NOT NULL
    AND pht.Name = 'Post Closed'
    AND (ph.Comment = '1' OR ph.Comment = '101')
    AND Text LIKE '{"OriginalQuestionIds"%'
), SingleDuplicates(Id, Text, OriginalId) as (
  select
    d.Id,
    d.Text,
--temporary hack to get the first valid dupe id
    CASE WHEN ISNUMERIC(LEFT(d.OriginalIds, CHARINDEX(',', d.OriginalIds))) = 1
      THEN CAST(LEFT(d.OriginalIds, CHARINDEX(',', d.OriginalIds) - 1) AS INT)
      ELSE CAST(d.OriginalIds AS INT)
    END as OriginalId
  from Duplicates d
)
select top 100
  count(d.Id) as [Dupe Count],
  d.OriginalId as [Post Link]
  --max(d.Text),
  --len(max(d.Text)) as [Len]
from
  SingleDuplicates d
--  inner join Posts p on p.Id = d.OriginalId
--where
--  d.OriginalId != 0
group by
  d.OriginalId
order by
  [Dupe Count] DESC


-- see also: http://data.stackexchange.com/english/query/144909/questions-closed-as-duplicate-of-other-questions

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results