0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

SELECT u.Id AS [User Link],
                            CONCAT('https://stackoverflow.com/users/', u.Id) AS [Profile Link],
                            u.DisplayName,
                            u.Location,
                            u.WebsiteUrl,
                            u.AboutMe,
                            u.Views,
                            u.UpVotes,
                            u.DownVotes,
                            questions.QuestionsPosted,
                            answers.AnswersPosted
             FROM Users u
        
             JOIN (
                    SELECT DISTINCT UserId
                    FROM Badges
                    WHERE LOWER(Name) IN ('terraform', 'amazon-web-services', 'docker', 'linux') AND
                          Class IN (1, 2, 3) AND
                          TagBased = 1
                  ) tag_badges
             ON tag_badges.UserId = u.Id
              
             LEFT JOIN
             (
               SELECT OwnerUserId, COUNT(*) AS QuestionsPosted
               FROM Posts
               JOIN PostTypes
               ON PostTypes.Id = Posts.PostTypeId
               WHERE PostTypes.Name = 'Question'
               GROUP BY Posts.OwnerUserId
             ) questions
             ON questions.OwnerUserId = u.Id
             
             LEFT JOIN
             (
               SELECT OwnerUserId, COUNT(*) AS AnswersPosted
               FROM Posts
               JOIN PostTypes
               ON PostTypes.Id = Posts.PostTypeId
               WHERE PostTypes.Name = 'Answer'
               GROUP BY Posts.OwnerUserId
             ) answers
             ON answers.OwnerUserId = u.Id
             
             WHERE LOWER(Location) LIKE '%amarante%' OR
                            LOWER(Location) LIKE '%castelo%' OR
                            LOWER(Location) LIKE '%lisbon%' OR
                            LOWER(Location) LIKE '%castelo de paiva%' OR
                            LOWER(Location) LIKE '%paã§os de ferreira%' OR
                            LOWER(Location) LIKE '%braga%' OR
                            LOWER(Location) LIKE '%viana do castelo%' OR
                            LOWER(Location) LIKE '%penafiel%' OR
                            LOWER(Location) LIKE '%sanfins do douro%' OR
                            LOWER(Location) LIKE '%vilarinho das paranheiras%' OR
                            LOWER(Location) LIKE '%chaves%' OR
                            LOWER(Location) LIKE '%boticas%' OR
                            LOWER(Location) LIKE '%braganca%' OR
                            LOWER(Location) LIKE '%vila real%' OR
                            LOWER(Location) LIKE '%miranda do douro%' OR
                            LOWER(Location) LIKE '%carrazedo%' OR
                            LOWER(Location) LIKE '%vilarinho de arcos%' OR
                            LOWER(Location) LIKE '%mirandela%' OR
                            LOWER(Location) LIKE '%macedo de cavaleiros%' OR
                            LOWER(Location) LIKE '%vinhais%' OR
                            LOWER(Location) LIKE '%peso da rã©gua municipality%' OR
                            LOWER(Location) LIKE '%lamego%' OR
                            LOWER(Location) LIKE '%dalvares%' OR
                            LOWER(Location) LIKE '%tabuaco%' OR
                            LOWER(Location) LIKE '%santa marta de penaguiao%' OR
                            LOWER(Location) LIKE '%torre de moncorvo%' OR
                            LOWER(Location) LIKE '%moncao%' OR
                            LOWER(Location) LIKE '%caminha%' OR
                            LOWER(Location) LIKE '%paredes de coura%' OR
                            LOWER(Location) LIKE '%ponte da barca%' OR
                            LOWER(Location) LIKE '%vila praia de ancora%' OR
                            LOWER(Location) LIKE '%arcos de valdevez%' OR
                            LOWER(Location) LIKE '%maia%' OR
                            LOWER(Location) LIKE '%santo tirso%' OR
                            LOWER(Location) LIKE '%paredes%' OR
                            LOWER(Location) LIKE '%carvalhosa%' OR
                            LOWER(Location) LIKE '%porto%' OR
                            LOWER(Location) LIKE '%sobrado de paiva%' OR
                            LOWER(Location) LIKE '%sobrosa%' OR
                            LOWER(Location) LIKE '%lousada%' OR
                            LOWER(Location) LIKE '%guimarã£es%' OR
                            LOWER(Location) LIKE '%marco%' OR
                            LOWER(Location) LIKE '%vila nova de gaia%' OR
                            LOWER(Location) LIKE '%olival%' OR
                            LOWER(Location) LIKE '%ponte de lima%' OR
                            LOWER(Location) LIKE '%paredes da beira%' OR
                            LOWER(Location) LIKE '%coimbra%' OR
                            LOWER(Location) LIKE '%mira%' OR
                            LOWER(Location) LIKE '%cernache%' OR
                            LOWER(Location) LIKE '%condeixa-a-nova%' OR
                            LOWER(Location) LIKE '%alfarelos%' OR
                            LOWER(Location) LIKE '%soure municipality%' OR
                            LOWER(Location) LIKE '%ceira%' OR
                            LOWER(Location) LIKE '%montemor-o-velho%' OR
                            LOWER(Location) LIKE '%granja do ulmeiro%' OR
                            LOWER(Location) LIKE '%carapinheira%' OR
                            LOWER(Location) LIKE '%sao joao do campo%' OR
                            LOWER(Location) LIKE '%miranda do corvo%' OR
                            LOWER(Location) LIKE '%viseu%' OR
                            LOWER(Location) LIKE '%arganil%' OR
                            LOWER(Location) LIKE '%tondela%' OR
                            LOWER(Location) LIKE '%castelejo%' OR
                            LOWER(Location) LIKE '%ãgueda municipality%' OR
                            LOWER(Location) LIKE '%campia%' OR
                            LOWER(Location) LIKE '%mesao frio%' OR
                            LOWER(Location) LIKE '%tarouca%' OR
                            LOWER(Location) LIKE '%nelas%' OR
                            LOWER(Location) LIKE '%satao%' OR
                            LOWER(Location) LIKE '%sao vicente de lafoes%' OR
                            LOWER(Location) LIKE '%parada de gonta%' OR
                            LOWER(Location) LIKE '%mangualde%' OR
                            LOWER(Location) LIKE '%santa comba dao%' OR
                            LOWER(Location) LIKE '%mamouros%' OR
                            LOWER(Location) LIKE '%murtede%' OR
                            LOWER(Location) LIKE '%cantanhede%' OR
                            LOWER(Location) LIKE '%mortagua%' OR
                            LOWER(Location) LIKE '%oliveira do bairro%' OR
                            LOWER(Location) LIKE '%mealhada%' OR
                            LOWER(Location) LIKE '%poiares%' OR
                            LOWER(Location) LIKE '%anadia%' OR
                            LOWER(Location) LIKE '%vilarinho do bairro%' OR
                            LOWER(Location) LIKE '%ovar%' OR
                            LOWER(Location) LIKE '%oliveira de azemã©is%' OR
                            LOWER(Location) LIKE '%esmoriz%' OR
                            LOWER(Location) LIKE '%sao roque%' OR
                            LOWER(Location) LIKE '%cucujaes%' OR
                            LOWER(Location) LIKE '%feira%' OR
                            LOWER(Location) LIKE '%sao joao da madeira%' OR
                            LOWER(Location) LIKE '%arouca%' OR
                            LOWER(Location) LIKE '%vale de cambra%' OR
                            LOWER(Location) LIKE '%santiago de riba-ul%' OR
                            LOWER(Location) LIKE '%aveiro%' OR
                            LOWER(Location) LIKE '%vagos%' OR
                            LOWER(Location) LIKE '%aguada de cima%' OR
                            LOWER(Location) LIKE '%palhaca%' OR
                            LOWER(Location) LIKE '%estarreja%' OR
                            LOWER(Location) LIKE '%mourisca do vouga%' OR
                            LOWER(Location) LIKE '%arrifana%'

fork query

edited nov 27

created nov 27 jon.doe34975