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

-- Option 1. Subquery

/*
SELECT
UserId,
COUNT(Id) AS num_of_badges
FROM Badges
WHERE UserId IN (
    SELECT
    Id
    FROM Users
    WHERE Location LIKE '%LV%' OR Location LIKE '%Latvia%' OR Location LIKE '%Latvija%'
    )
GROUP BY UserId
HAVING COUNT(Id) > 100
ORDER BY num_of_badges DESC;
*/

-- Option 2. CTE + Join. 
 
/*
WITH users_in_latvia AS (
    SELECT
    Id as UserId
    FROM Users
    WHERE Location LIKE '%LV%' OR Location LIKE '%Latvia%' OR Location LIKE '%Latvija%'
    ), users_over_100_badges AS (
    SELECT
    UserId,
    COUNT(Id) AS badge_num
    FROM Badges
    GROUP BY UserId
    HAVING COUNT(Id) > 100
    )
SELECT
t1.UserId,
t1.badge_num
FROM users_over_100_badges AS t1
JOIN users_in_latvia AS t2
ON t1.UserId = t2.UserId
ORDER BY t1.badge_num DESC;
*/

-- Option 3 (basically just shortened option 2). 

/*
WITH users_in_latvia AS (
    SELECT
    Id as UserId
    FROM Users
    WHERE Location LIKE '%LV%' OR Location LIKE '%Latvia%' OR Location LIKE '%Latvija%'
    )
SELECT
t1.UserId,
COUNT(t1.Id) AS badge_num
FROM Badges t1
JOIN users_in_latvia t2
ON t1.UserId = t2.UserId
GROUP BY t1.UserId
HAVING COUNT(t1.Id) > 100
ORDER BY badge_num DESC;
*/

-- Option 4. Temporary Table (this seems to be the quickest)
-- althgouh I would naturally, intuitively opt for option 1, unless
-- performance considerations are very substantial


SELECT
  Id AS UserID
INTO #users_in_latvia
FROM Users
WHERE Location LIKE '%LV%' OR Location LIKE '%Latvia%' OR Location LIKE '%Latvija%';

      
SELECT 
UserId,
COUNT(Id) AS num_of_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;

fork query

edited nov 28

created nov 27 DanyDant