0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for teachers and students of the Esperanto language

-- Pull users from Latvia to separate table
SELECT
Id AS UserId
INTO #users_in_latvia
FROM Users
WHERE Location LIKE '%LV%' OR Location LIKE '%Latvia%' OR Location LIKE '%Latvija%';

-- Pull users that have "editor" badge and are also from Latvia into separate temp table
SELECT DISTINCT
t1.UserId
INTO #lv_users_with_badge
FROM Badges t1
INNER JOIN #users_in_latvia t2
ON t1.UserId = t2.UserId
WHERE t1.Name = 'Editor';

-- pull all posts by users segregated (latvian + editor badge) in previous queries
-- with respective post creation date. And partitioning by Post Owner (UserId)
WITH ranked_by_date AS (
SELECT
    t1.Id AS PostId,
    t1.CreationDate AS CreationDate,
    t1.OwnerUserId AS UserId,
    -- partitioning by Post Owner (UserId), order by creation date
    -- from largest to smallest (latest to earliest)
    ROW_NUMBER() OVER (PARTITION BY t1.OwnerUserId ORDER BY t1.CreationDate DESC) AS row_num
FROM Posts t1
INNER JOIN #lv_users_with_badge t2
ON t1.OwnerUserId = t2.UserId
-- ensuring that post type = 1 (which is an Id of Question) as per PostTypes table
WHERE PostTypeId = 1
)
-- return just one top row for each user. top row would be the row of the latest post
SELECT
UserID,
PostId,
CreationDate
FROM ranked_by_date
WHERE row_num = 1;

fork query

edited nov 28

created nov 28 DanyDant