0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gardeners and landscapers

DECLARE @Year int = ##Year:int?2023##;
DECLARE @CountryName nvarchar(100) = ##CountryName:string?United States##;

SELECT
    u.Location AS Country,
    YEAR(p.CreationDate) AS Year,
    COUNT(DISTINCT u.Id) AS NumberOfActiveUsers
FROM
    Users u
LEFT JOIN Posts p ON u.Id = p.OwnerUserId AND YEAR(p.CreationDate) = @Year
LEFT JOIN Comments c ON u.Id = c.UserId AND YEAR(c.CreationDate) = @Year
LEFT JOIN Votes v ON u.Id = v.UserId AND YEAR(v.CreationDate) = @Year
WHERE
    LOWER(u.Location) LIKE LOWER('%' + @CountryName + '%')
    AND (p.Id IS NOT NULL OR c.Id IS NOT NULL OR v.Id IS NOT NULL)
GROUP BY
    u.Location, YEAR(p.CreationDate)
ORDER BY
    NumberOfActiveUsers DESC;

view current version

edited jan 31

created jan 31 anonymous