0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and amateur woodworkers

--SELECT TOP 100 *
--FROM Users
--WHERE LOWER(Location) LIKE '%latvia%'

--SELECT Location, COUNT(Id) loc_count
--FROM Users
--WHERE LOWER(Location) LIKE '%latvia%' OR Location LIKE '%Riga %' OR Location LIKE '%Rīga %' OR Location LIKE '%Riga,%'
--GROUP BY Location
--ORDER BY loc_count DESC

--WITH valid_users AS
--(SELECT 

--SELECT Users.Id, COUNT(Badges.Id) AS badge_count
--FROM Users
--INNER JOIN Badges
--ON 
--Users.Id = Badges.UserId
--WHERE LOWER(Location) LIKE '%latvia%' OR Location LIKE '%Riga %' OR Location LIKE '%Rīga %' OR Location LIKE '%Riga,%'
--GROUP BY Users.Id
--HAVING COUNT(Badges.Id) > 100
--ORDER BY COUNT(Badges.Id) DESC;

WITH lv_users AS
(SELECT Users.Id AS user_id, Badges.Id AS badges_id
FROM Users
INNER JOIN Badges
ON Users.Id = Badges.UserId
WHERE LOWER(Location) LIKE '%latvia%' OR Location LIKE '%Riga %' OR Location LIKE '%Rīga %' OR Location LIKE '%Riga,%'
),

users_over_100 AS
(SELECT user_id, COUNT(badges_id) AS badge_count
FROM lv_users
GROUP BY user_id
HAVING COUNT(badges_id) > 100
--ORDER BY badge_count DESC
)

--SELECT users_over_100.*
--FROM users_over_100
--INNER JOIN 
--  (SELECT DISTINCT OwnerUserId
--  FROM Posts
--  WHERE CreationDate BETWEEN '2021-01-01' AND '2021-12-31'
--  ) AS valid_posts
--ON users_over_100.user_id = valid_posts.OwnerUserId
--ORDER BY badge_count DESC


--WITH t1 AS
--(SELECT TOP 100 *
--FROM Posts
--INNER JOIN PostNotices
--ON Posts.Id = PostNotices.PostId
--)

--SELECT TOP 100 * 
--FROM PostNotices
--WHERE CreationDate BETWEEN '2021-01-01' AND '2021-12-31'

--SELECT *
--FROM Posts
--WHERE OwnerUserId = '1857266'
--ORDER BY CreationDate

SELECT DISTINCT Name from Badges
ORDER BY Name

fork query

edited dec 3

created dec 3 anonymous