Sites mentioned in Community Ads answers posted in year X

0

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A for serious players and enthusiasts of poker

-- result table, don't rename and keep the site column
CREATE TABLE #results (site nvarchar(250), url nvarchar(250));

DECLARE @sql NVARCHAR(MAX) = ''   -- holds build up sql string

-- build one biq union sql, for each db
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
SELECT ''' + name + ''',
         SUBSTRING(SUBSTRING(a.Body, 13, LEN(a.Body) - 13), 1,
  CHARINDEX(''"'', SUBSTRING(a.Body, 13, LEN(a.Body) - 13)) - 1)
  FROM ' + quotename(name) + '.dbo.Posts AS a
  INNER JOIN ' + quotename(name) + '.dbo.Posts AS q ON q.Id = a.ParentId
  INNER JOIN ' + quotename(name) + '.dbo.PostTags AS pt ON q.Id = pt.PostId
  INNER JOIN ' + quotename(name) + '.dbo.Tags AS t ON t.Id = pt.TagId
  WHERE t.TagName = ''community-ads''
    AND YEAR(q.CreationDate) = ' + CAST(##year:int## AS NVARCHAR) + '
    AND (a.Body LIKE ''<p><a href="http%://%.stackexchange.com%"%><img %''
      OR a.Body LIKE ''<p><a href="http%://stackoverflow.com%"%><img %''
      OR a.Body LIKE ''<p><a href="http%://mathoverflow.net%"%><img %''
      OR a.Body LIKE ''<p><a href="http%://superuser.com%"%><img %''
      OR a.Body LIKE ''<p><a href="http%://serverfault.com%"%><img %''
      OR a.Body LIKE ''<p><a href="http%://askubuntu.com%"%><img %''
      OR a.Body LIKE ''<p><a href="http%://stackapps.com%"%><img %'')
'
FROM sys.databases
WHERE database_id > 5
  AND name LIKE '%.Meta'

EXEC (@sql)

-- convert links to domains;
-- let's consider ads like https://rpg.meta.stackexchange.com/a/9081/22219
-- linking to another site's meta as well
-- links to chat.stackexchange.com are deleted, it's hard to determine to which site they belong
UPDATE #results
  SET url = SUBSTRING(url, IIF(PATINDEX('http://%', url) > 0, 8, 9), 250);
DELETE FROM #results
  WHERE PATINDEX('chat.%', url) = 1;
UPDATE #results
  SET url = IIF(CHARINDEX('/', url) = 0, url,
                SUBSTRING(url, 1, CHARINDEX('/', url) - 1));
UPDATE #results
  SET url = IIF(PATINDEX('%meta.%', url) = 0, url,
                SUBSTRING(url, 1, PATINDEX('%meta.%', url) - 1) +
                SUBSTRING(url, PATINDEX('%meta.%', url) + 5, 250));

SELECT 'https://' + url,
  COUNT(*) AS 'Number of links',
  COUNT(DISTINCT(site)) AS 'Number of source sites'
  FROM #results
  -- Links to the site itself or to its meta don't count ...
  WHERE url NOT LIKE '%' + (

  -- cf. https://data.stackexchange.com/worldbuildingme/query/1054492/convert-meta-database-name-to-main-site-domain
  LOWER(
    CASE
      WHEN site = 'StackExchange.MathOverflow.Meta' THEN 'mathoverflow.net'
      WHEN site = 'StackExchange.Ubuntu.Meta' THEN 'askubuntu.com'
      WHEN PATINDEX('StackOverflow.%.Meta', site) > 0
        THEN SUBSTRING(site, 15, LEN(site) - 19) + '%.stackoverflow.com'
      WHEN PATINDEX('StackExchange.%.Meta', site) > 0
        THEN CASE SUBSTRING(site, 15, LEN(site) - 19)
          WHEN 'Audio' THEN 'video'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Writers' THEN 'writing'
          ELSE SUBSTRING(site, 15, LEN(site) - 19)
        END + '%.stackexchange.com'
      ELSE SUBSTRING(site, 0, LEN(site) - 4) + '.com'
    END)
    
    ) + '%'
  GROUP BY url
  ORDER BY COUNT(*) DESC, COUNT(DISTINCT(site)) DESC
     
DROP TABLE #results

Enter Parameters

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