Tuesday, April 26, 2016

TOP 100 SO users from Bangladesh

Top 100 Bangladeshi SO user

TOP 50 StackOverflow user in Bangladesh

Top Users by Number of Bounties Won in Bangladesh

-- Top Users by Number of Bounties Won in Bangladesh

SELECT Top 150
Posts.OwnerUserId As [User Link], COUNT(*) As BountiesWon, Users.Location
FROM Votes
  INNER JOIN Posts ON Votes.PostId = Posts.Id
  Inner JOIN Users ON Posts.OwnerUserId = Users.Id
WHERE
  VoteTypeId=9 and LOWER(Users.Location) LIKE LOWER('%Bangladesh')
GROUP BY
  Posts.OwnerUserId, Users.Location
ORDER BY
BountiesWon DESC

Top Users by Number of Bounties Won


-- Top Users by Number of Bounties Won

SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY COUNT(*) Desc) as PositionNumber,
  Posts.OwnerUserId As [User Link], COUNT(*) As BountiesWon
FROM Votes
  INNER JOIN Posts ON Votes.PostId = Posts.Id
WHERE
  VoteTypeId=9
GROUP BY
  Posts.OwnerUserId
ORDER BY
  BountiesWon DESC

http://meta.stackexchange.com/questions/49943/interesting-queries-on-data-explorer


http://data.stackexchange.com/stackoverflow/revision/485914/615634/top-100-bangladeshi

Select TOP(100) ROW_NUMBER() OVER (ORDER BY Reputation Desc) as PositionNumber, Id [User Link], DisplayName, Age, Reputation, WebsiteUrl, Location
From Users
Where LOWER(Location) LIKE LOWER('%Bangladesh')
ORDER BY
Reputation DESC;

http://data.stackexchange.com/stackoverflow/query/949/what-is-my-accepted-answer-percentage-rate
-- What is my accepted answer percentage rate
-- On avg how often are answers I give, accepted

DECLARE @UserId int = ##UserId##

SELECT 
    (CAST(Count(a.Id) AS float) / (SELECT Count(*) FROM Posts WHERE OwnerUserId = @UserId AND PostTypeId = 2) * 100) AS AcceptedPercentage
FROM
    Posts q
  INNER JOIN
    Posts a ON q.AcceptedAnswerId = a.Id
WHERE
    a.OwnerUserId = @UserId
  AND
    a.PostTypeId = 2

Ranking in StackOverflow

http://data.stackexchange.com/stackoverflow/revision/478416/606174/ranking-in-stackoverflow
WITH RankingsInSO AS (
SELECT Id, RankingInSO = ROW_NUMBER() OVER(ORDER BY Reputation DESC)
FROM Users
)
,CountsInSO AS (
SELECT CountInSO = COUNT(*)
FROM Users
WHERE Reputation > 100
)
,Rankings AS (
SELECT Id, RankingInBangladesh = ROW_NUMBER() OVER(ORDER BY Reputation DESC), DisplayName, Age, Reputation, WebsiteUrl, Location
FROM Users
Where LOWER(Location) LIKE LOWER('%Bangladesh')
)
,Counts AS (
SELECT Count = COUNT(*)
FROM Users
WHERE Reputation > 100 and LOWER(Location) LIKE LOWER('%Bangladesh')
)
SELECT R.Id, R.Id[User Link], R.RankingInBangladesh, CAST(R.RankingInBangladesh AS decimal(20, 5)) / (SELECT Count FROM Counts) AS PercentileInBangladesh, RSO.RankingInSO as RankingInSO, CAST(RSO.RankingInSO AS decimal(20, 5)) / (SELECT CountInSO FROM CountsInSO) AS PercentileInSO, R.Age, R.Reputation, R.WebsiteUrl, R.Location
FROM Rankings R, RankingsInSO RSO
WHERE R.Id = 2293534 and R.Id = RSO.Id

My Rank in Bangladesh
https://data.stackexchange.com/stackoverflow/revision/478354/606098/my-rank-in-bangladesh

-- StackOverflow Rank and Percentile

WITH Rankings AS (
SELECT Id, RankingInBangladesh = ROW_NUMBER() OVER(ORDER BY Reputation DESC), DisplayName, Age, Reputation, WebsiteUrl, Location
FROM Users
Where LOWER(Location) LIKE LOWER('%Bangladesh')
)
,Counts AS (
SELECT Count = COUNT(*)
FROM Users
WHERE Reputation > 100 and LOWER(Location) LIKE LOWER('%Bangladesh')

)
SELECT Id, Id[User Link], RankingInBangladesh, CAST(RankingInBangladesh AS decimal(20, 5)) / (SELECT Count FROM Counts) AS Percentile, Age, Reputation, WebsiteUrl, Location
FROM Rankings
WHERE Id = 2293534

No comments:

Post a Comment