0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for designers and implementers of computer programming languages

-- Separate Users from Latvia into separate temp table
SELECT
  Id AS UserID
INTO #users_in_latvia
FROM Users
WHERE Location LIKE '%LV%' OR Location LIKE '%Latvia%' OR Location LIKE '%Latvija%';

-- Separate Users from Latvia which have more than 100 badges into temp table
SELECT 
UserId,
COUNT(Id) AS num_of_badges
INTO #lv_users_with_100_badges
FROM Badges
WHERE UserId IN (
    SELECT
      UserId
    FROM #users_in_latvia
    )
GROUP BY UserId
HAVING COUNT(Id) > 100
ORDER BY num_of_badges DESC;

-- Separate posts created in 2021 by users from Latvia with >100 badges into temp table
SELECT
t1.OwnerUserId AS UserId
INTO #user_with_posts_in_2021
FROM Posts t1
JOIN #lv_users_with_100_badges t2
ON t1.OwnerUserId = t2.UserId
WHERE CreationDate >= '2021-01-01 00:00:00' AND CreationDate < '2022-01-01 00:00:00'
GROUP BY t1.OwnerUserId;

-- Return the final result - users which have post(a) in 2021, and their respective badge count
SELECT
UserId,
num_of_badges
FROM #lv_users_with_100_badges
WHERE UserId IN (SELECT UserId FROM #user_with_posts_in_2021)
ORDER BY num_of_badges DESC;

fork query

edited nov 28

created nov 27 DanyDant