Actual badge queries used by StackExchange for use on SEDE

Question

SEDE has been a great tool to poke into SE data. I have seen some saved queries on it.

• Is there a definitive [official] list of queries used for badges on there? [apparently not]
• Is there a way to distinguish/quickly find queries used by SE itself and not user-contributed queries?

Since there is no definitive list, how many queries like this one Legendary are available from questions/answers on meta?

Re badge queries (subset of question) >
Keep in mind that some of the badges depend on information that is not available in the data dump (like sportsmanship, talkative and so on)

Answer 1

Is there a definitive [official] list of queries used for badges on there?

There is.. It's a bit out of date though.

The post is apparently too long; part 1 of 2

<?xml version="1.0" encoding="utf-8"?>
<Badges>
  <row>
    <Id>1</Id>
    <Class>3</Class>
    <Name>Teacher</Name>
    <Description>Answered first question with at least one up vote</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one upmodded answer */ (select distinct OwnerUserId from Posts p with (nolock) where PostTypeId = 2 and Score &gt; 0) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Teacher') and u.Id = x.OwnerUserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>2</Id>
    <Class>3</Class>
    <Name>Student</Name>
    <Description>Asked first question with at least one up vote</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one upmodded question */ (select distinct OwnerUserId from Posts p with (nolock) where PostTypeId = 1 and Score &gt; 0) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Student') and u.Id = x.OwnerUserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>3</Id>
    <Class>3</Class>
    <Name>Editor</Name>
    <Description>First edit</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one revision that isnt the first */ (select UserId from PostHistory ph where PostHistoryTypeId in (4,5) group by UserId) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Editor') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>4</Id>
    <Class>3</Class>
    <Name>Cleanup</Name>
    <Description>First rollback</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one rollback */ (select UserId from PostHistory ph where PostHistoryTypeId in (7,8,9) group by UserId) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Cleanup') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>5</Id>
    <Class>3</Class>
    <Name>Organizer</Name>
    <Description>First retag</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who own the current version where tags are different than first version */ (select UserId from PostHistory ph where PostHistoryTypeId = 6 and PostId not in (select p.Id from Posts p where p.OwnerUserId = ph.UserId and DeletionDate is null) group by UserId) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Organizer') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>6</Id>
    <Class>3</Class>
    <Name>Supporter</Name>
    <Description>First up vote</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one upvote */ (select distinct UserId from Posts2Votes with (nolock) where VoteTypeId = 2) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Supporter') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>7</Id>
    <Class>3</Class>
    <Name>Critic</Name>
    <Description>First down vote</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one downvote */ (select distinct UserId from Posts2Votes with (nolock) where VoteTypeId = 3) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Critic') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>8</Id>
    <Class>3</Class>
    <Name>Citizen Patrol</Name>
    <Description>First flagged post</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one offensive flag */ (select distinct UserId from Posts2Votes with (nolock) where VoteTypeId in (4,12,13)) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Citizen Patrol') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>9</Id>
    <Class>3</Class>
    <Name>Autobiographer</Name>
    <Description>Completed all user profile fields</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids where common user table fields are non-null */ (select Id UserId from Users u with (nolock) where isnull(u.Email, '') &lt;&gt; '' and isnull(u.DisplayName, '') &lt;&gt; '' and isnull(u.WebsiteUrl, '') &lt;&gt; '' and isnull(u.RealName, '') &lt;&gt; '' and isnull(u.Birthday, '') &lt;&gt; '' and isnull(u.Location, '') &lt;&gt; '' and isnull(u.AboutMe, '') &lt;&gt; '') x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Autobiographer') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>10</Id>
    <Class>3</Class>
    <Name>Scholar</Name>
    <Description>First accepted answer on your own question</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with at least one accepted answer */ (select distinct OwnerUserId from Posts p with (nolock) where AcceptedAnswerId is not null and PostTypeId = 1 and DeletionDate is null) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Scholar') and u.Id = x.OwnerUserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>11</Id>
    <Class>2</Class>
    <Name>Taxonomist</Name>
    <Description>Created a tag used by 50 questions</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who created tags used more than 50 times */ (select max(UserId) as UserId from Tags where Count &gt;= 50 group by UserId) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Taxonomist') and u.Id = x.UserId</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>12</Id>
    <Class>2</Class>
    <Name>Strunk &amp; White</Name>
    <Description>Edited 100 entries</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have generated 100 revisions on stuff that isnt theirs */ (select max(UserId) as UserId from PostHistory ph where PostHistoryTypeId in (4,5) and PostId not in (select p.Id from Posts p where p.OwnerUserId = ph.UserId and DeletionDate is null) group by UserId having COUNT(*) &gt; 100) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Strunk &amp; White') and u.Id = x.UserId</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>13</Id>
    <Class>2</Class>
    <Name>Yearling</Name>
    <Description>Active member for a year, earning at least 200 reputation</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>SELECT x.UserId, x.ItemCount, y.BadgeCount FROM /* list users who have been members for n years with at least 200 rep for each year */ (select Id as UserId, CAST(DATEDIFF(dd, CreationDate, GETUTCDATE()) / 365.242199 AS int) as ItemCount from Users where (DATEDIFF(dd, CreationDate, GETUTCDATE()) / 365.242199) &gt;= 1 and Reputation &gt; (DATEDIFF(dd, CreationDate, GETUTCDATE()) / 365.242199) * 200) x LEFT OUTER JOIN /* list number of badges of this type for all users */ (SELECT Max(u2b.UserId) UserId, Count(*) BadgeCount FROM Users2Badges u2b WITH (nolock) INNER JOIN Badges b ON u2b.BadgeId = b.Id WHERE b.Name = 'Yearling' GROUP BY u2b.UserId, u2b.BadgeId) y ON x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ WHERE (ItemCount &gt; BadgeCount OR BadgeCount IS NULL)</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>14</Id>
    <Class>3</Class>
    <Name>Self-Learner</Name>
    <Description>Answered your own question with at least 3 up votes</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have answered their own question with at least 3 upvotes */ (select distinct OwnerUserId from Posts p with (nolock) where OwnerUserId in (select distinct OwnerUserId from Posts answers with (nolock) where answers.PostTypeId = 2 and answers.ParentId = p.Id and answers.Score &gt;= 3 and answers.DeletionDate is null) and p.PostTypeId = 1 and p.DeletionDate is null) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Self-Learner') and u.Id = x.OwnerUserId</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>15</Id>
    <Class>2</Class>
    <Name>Generalist</Name>
    <Description>Active in many different tags</Description>
    <Single>false</Single>
    <Secret>true</Secret>
    <TagBased>false</TagBased>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>17</Id>
    <Class>2</Class>
    <Name>Necromancer</Name>
    <Description>Answered a question more than 60 days later with at least 5 votes</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of responses with 5+ score posted 60+ days after question */ (select max(a.OwnerUserId) UserId, count(*) ItemCount from Posts q with (nolock) inner join Posts a on a.ParentId = q.Id where q.PostTypeId = 1 and a.PostTypeId = 2 and datediff(d, q.CreationDate, a.CreationDate) &gt;= 60 and a.Score &gt;= 5 group by a.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Necromancer' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>18</Id>
    <Class>2</Class>
    <Name>Guru</Name>
    <Description>Accepted answer and voted up 40 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* get accepted answers with score &gt; 40 */ (select max(accepted.OwnerUserId) UserId, count(*) ItemCount from Posts p inner join Posts accepted on accepted.Id = p.AcceptedAnswerId inner join Posts q on q.Id = accepted.ParentId where p.DeletionDate is null and accepted.Score &gt;= 40 and accepted.OwnerUserId &lt;&gt; q.OwnerUserId group by accepted.OwnerUserId) x left outer join /* list number of badges of this type for all users */(select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Guru' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>

Answer 2

Part 2 of 2

  <row>
    <Id>19</Id>
    <Class>2</Class>
    <Name>Enlightened</Name>
    <Description>First answer was accepted with at least 10 up votes</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* get first accepted answers with score of at least 10 */ (select max(accepted.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) inner join Posts accepted on accepted.Id = p.AcceptedAnswerId inner join Posts q on q.Id = accepted.ParentId where p.AcceptedAnswerId is not null and accepted.Score &gt;= 10 and p.AcceptedAnswerId =  (select min(answers.Id) from Posts answers with (nolock) where answers.ParentId = p.Id and PostTypeId = 2 and answers.OwnerUserId &lt;&gt; q.OwnerUserId and DeletionDate is null) and p.DeletionDate is null and p.PostTypeId = 1 group by accepted.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Enlightened' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>10</AwardFrequency>
  </row>
  <row>
    <Id>20</Id>
    <Class>3</Class>
    <Name>Nice Question</Name>
    <Description>Question voted up 10 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of questions with score &gt; 10 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 1 and p.DeletionDate is null and (p.Score &gt;= 10) group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Nice Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>21</Id>
    <Class>2</Class>
    <Name>Good Question</Name>
    <Description>Question voted up 25 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of questions with score &gt; 25 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 1 and p.DeletionDate is null and (p.Score &gt;= 25) group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Good Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>22</Id>
    <Class>1</Class>
    <Name>Great Question</Name>
    <Description>Question voted up 100 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of questions with score &gt; 100 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 1 and p.DeletionDate is null and p.Score &gt;= 100 group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Great Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>3</AwardFrequency>
  </row>
  <row>
    <Id>23</Id>
    <Class>3</Class>
    <Name>Nice Answer</Name>
    <Description>Answer voted up 10 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of answers with score &gt; 10 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 2 and p.DeletionDate is null and (p.Score &gt;= 10) group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Nice Answer' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>24</Id>
    <Class>2</Class>
    <Name>Good Answer</Name>
    <Description>Answer voted up 25 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of answers with score &gt; 25 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 2 and p.DeletionDate is null and (p.Score &gt;= 25) group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Good Answer' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>25</Id>
    <Class>1</Class>
    <Name>Great Answer</Name>
    <Description>Answer voted up 100 times</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of answers with score &gt; 100 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 2 and p.DeletionDate is null and p.Score &gt;= 100 group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Great Answer' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>3</AwardFrequency>
  </row>
  <row>
    <Id>26</Id>
    <Class>3</Class>
    <Name>Popular Question</Name>
    <Description>Asked a question with 1,000 views</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of questions with views &gt; 1000 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 1 and p.DeletionDate is null and (p.ViewCount &gt;= 1000) and p.OwnerUserId is not null group by p.OwnerUserId) x left hash join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Popular Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>27</Id>
    <Class>2</Class>
    <Name>Notable Question</Name>
    <Description>Asked a question with 2,500 views</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of questions with views &gt; 2500 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 1 and p.DeletionDate is null and (p.ViewCount &gt;= 2500) and p.OwnerUserId is not null group by p.OwnerUserId) x left hash join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Notable Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>28</Id>
    <Class>1</Class>
    <Name>Famous Question</Name>
    <Description>Asked a question with 10,000 views</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of questions with views &gt; 10000 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.PostTypeId = 1 and p.DeletionDate is null and p.ViewCount &gt;= 10000 and p.OwnerUserId is not null group by p.OwnerUserId) x left hash join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Famous Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>3</AwardFrequency>
  </row>
  <row>
    <Id>29</Id>
    <Class>2</Class>
    <Name>Hacker</Name>
    <Description>Contributed to Stack Overflow in an unusual way</Description>
    <Single>true</Single>
    <Secret>true</Secret>
    <TagBased>false</TagBased>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>31</Id>
    <Class>3</Class>
    <Name>Commentator</Name>
    <Description>Left 10 comments</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with 10 comments */ (select max(UserId) as UserId from PostComments pc with (nolock) where pc.DeletionDate is null group by UserId having count(UserId) &gt;= 10) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Commentator') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>32</Id>
    <Class>2</Class>
    <Name>Civic Duty</Name>
    <Description>Voted 300 times</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with 300+ votes */ (select max(UserId) as UserId from Posts2Votes p2v with (nolock) where p2v.DeletionDate is null and (p2v.VoteTypeId = 2 or p2v.VoteTypeId = 3) group by UserId having count(UserId) &gt;= 300) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Civic Duty') and u.Id = x.UserId</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>33</Id>
    <Class>2</Class>
    <Name>Favorite Question</Name>
    <Description>Question favorited by 25 users</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of answers with favorites &gt; 25 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.Id in (select max(PostId) from Posts2Votes p2v where VoteTypeId = 5 and p2v.DeletionDate is null group by PostId having count(*) &gt;= 25) and p.DeletionDate is null group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Favorite Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>5</AwardFrequency>
  </row>
  <row>
    <Id>34</Id>
    <Class>1</Class>
    <Name>Stellar Question</Name>
    <Description>Question favorited by 100 users</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list number of answers with favorites &gt; 100 */ (select max(p.OwnerUserId) UserId, count(*) ItemCount from Posts p with (nolock) where p.Id in (select max(PostId) from Posts2Votes p2v where VoteTypeId = 5 and p2v.DeletionDate is null group by PostId having count(*) &gt;= 100) and p.DeletionDate is null group by p.OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Stellar Question' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>3</AwardFrequency>
  </row>
  <row>
    <Id>35</Id>
    <Class>3</Class>
    <Name>Disciplined</Name>
    <Description>Deleted own post with score of 3 or higher</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have deleted own post with 3+ upvotes */ (select max(p.OwnerUserId) as UserId from Posts p where p.DeletionDate is not null and p.Score &gt;= 3 and p.OwnerUserId = (select top 1 UserId from PostHistory ph where ph.PostId = p.Id and ph.PostHistoryTypeId = 12 order by ph.CreationDate desc) group by p.OwnerUserId) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Disciplined') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>36</Id>
    <Class>3</Class>
    <Name>Peer Pressure</Name>
    <Description>Deleted own post with score of -3 or lower</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have deleted own post with 3+ downvotes */ (select max(p.OwnerUserId) as UserId from Posts p where p.PostTypeId in (1,2) and p.DeletionDate is not null and p.Score &lt;= -3 and p.OwnerUserId = (select top 1 UserId from PostHistory ph where ph.PostId = p.Id and ph.PostHistoryTypeId = 12 order by ph.CreationDate desc) group by p.OwnerUserId) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Peer Pressure') and u.Id = x.UserId</Command>
    <AwardFrequency>1</AwardFrequency>
  </row>
  <row>
    <Id>37</Id>
    <Class>1</Class>
    <Name>Populist</Name>
    <Description>Provided an answer that outscored an accepted answer with 10 votes by 2x</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list users whose non-accepted answers are more than 2x the score of the accepted answer */ (select MAX(OwnerUserId) as UserId, COUNT(*) as ItemCount from ( select OwnerUserId from ( select p.Id, accepted.Score as AcceptedScore from Posts p inner join Posts accepted on p.AcceptedAnswerId = accepted.Id where p.AcceptedAnswerId is not null and p.DeletionDate is null and accepted.Score &gt; 10 ) x inner join Posts answers on answers.ParentId = x.Id where Score &gt; AcceptedScore * 2 and answers.DeletionDate is null and (select max(Score) from Posts temp where temp.ParentId = x.Id and temp.DeletionDate is null) = Score ) as rollup group by OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Populist' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>5</AwardFrequency>
  </row>
  <row>
    <Id>38</Id>
    <Class>3</Class>
    <Name>Tumbleweed</Name>
    <Description>Asked a question with no answers, no comments, and low views for a week</Description>
    <Single>true</Single>
    <Secret>true</Secret>
    <TagBased>false</TagBased>
    <AwardFrequency>5</AwardFrequency>
  </row>
  <row>
    <Id>39</Id>
    <Class>2</Class>
    <Name>Enthusiast</Name>
    <Description>Visited the site each day for 30 days</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select v.UserId [Id] from vBadgeEnthusiast v where not exists (select 1 from Users2Badges u2b join Badges b on b.Id = u2b.BadgeId and b.Name = 'Enthusiast' where u2b.UserId = v.UserId)</Command>
    <AwardFrequency>10</AwardFrequency>
  </row>
  <row>
    <Id>40</Id>
    <Class>1</Class>
    <Name>Fanatic</Name>
    <Description>Visited the site each day for 100 days</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select v.UserId [Id] from vBadgeFanatic v where not exists (select 1 from Users2Badges u2b join Badges b on b.Id = u2b.BadgeId and b.Name = 'Fanatic' where u2b.UserId = v.UserId)</Command>
    <AwardFrequency>10</AwardFrequency>
  </row>
  <row>
    <Id>94</Id>
    <Class>2</Class>
    <Name>Pundit</Name>
    <Description>Left 10 comments with score of 10 or more</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids with 10 comments of score &gt;= 10 */ (select UserId from PostComments where UserId is not null and DeletionDate is null and Score &gt;= 10 group by UserId having COUNT(*) &gt;= 10) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Pundit') and u.Id = x.UserId</Command>
    <AwardFrequency>2</AwardFrequency>
  </row>
  <row>
    <Id>95</Id>
    <Class>1</Class>
    <Name>Reversal</Name>
    <Description>Provided answer of +20 score to a question of -5 score</Description>
    <Single>false</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select x.UserId, x.ItemCount, y.BadgeCount from /* list users who have answers with score &gt;= 20 on a question of score &lt;= -5 */ (select MAX(OwnerUserId) as UserId, COUNT(*) as ItemCount from (select a.OwnerUserId, a.Id from Posts a inner join Posts q on a.ParentId = q.Id where a.PostTypeId = 2 and a.DeletionDate is null and q.DeletionDate is null and a.Score &gt;= 20 and q.Score &lt;= -5) as rollup group by OwnerUserId) x left outer join /* list number of badges of this type for all users */ (select max(u2b.UserId) UserId, count(*) BadgeCount from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Reversal' group by u2b.UserId, u2b.BadgeId) y on x.UserId = y.UserId /* only award new badges if the matching items outnumber the current badge count */ where (ItemCount &gt; BadgeCount or BadgeCount is null)</Command>
    <AwardFrequency>3</AwardFrequency>
  </row>
  <row>
    <Id>144</Id>
    <Class>3</Class>
    <Name>Mortarboard</Name>
    <Description>Hit the daily reputation cap for the first time</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have hit the rep cap for 1 day */ (select TargetUserId as UserId from (select TargetUserId, CONVERT(char(8), CreationDate, 112) as DayPeriod, SUM(TargetRepChange) as DayRep from Posts2Votes where TargetRepChange is not null and DeletionDate is null group by TargetUserId, CONVERT(char(8), CreationDate, 112) having SUM(TargetRepChange) &gt;= 200) as x group by TargetUserId having COUNT(*) &gt;= 1) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Mortarboard') and u.Id = x.UserId</Command>
    <AwardFrequency>10</AwardFrequency>
  </row>
  <row>
    <Id>145</Id>
    <Class>2</Class>
    <Name>Epic</Name>
    <Description>Hit the daily reputation cap on 50 days</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have hit the rep cap for 50 days */ (select TargetUserId as UserId from (select TargetUserId, CONVERT(char(8), CreationDate, 112) as DayPeriod, SUM(TargetRepChange) as DayRep from Posts2Votes where TargetRepChange is not null and DeletionDate is null group by TargetUserId, CONVERT(char(8), CreationDate, 112) having SUM(TargetRepChange) &gt;= 200) as x group by TargetUserId having COUNT(*) &gt;= 50) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Epic') and u.Id = x.UserId</Command>
    <AwardFrequency>10</AwardFrequency>
  </row>
  <row>
    <Id>146</Id>
    <Class>1</Class>
    <Name>Legendary</Name>
    <Description>Hit the daily reputation cap on 150 days</Description>
    <Single>true</Single>
    <Secret>false</Secret>
    <TagBased>false</TagBased>
    <Command>select u.Id from Users u with (nolock), /* return all userids who have hit the rep cap for 150 days */ (select TargetUserId as UserId from (select TargetUserId, CONVERT(char(8), CreationDate, 112) as DayPeriod, SUM(TargetRepChange) as DayRep from Posts2Votes where TargetRepChange is not null and DeletionDate is null group by TargetUserId, CONVERT(char(8), CreationDate, 112) having SUM(TargetRepChange) &gt;= 200) as x group by TargetUserId having COUNT(*) &gt;= 150) x where u.Id not in /* return all userids who do NOT have the badge */ (select UserId from Users2Badges u2b with (nolock) inner join Badges b on u2b.BadgeId = b.Id where b.Name = 'Legendary') and u.Id = x.UserId</Command>
    <AwardFrequency>10</AwardFrequency>
  </row>
</Badges>

Answer 3

Is there a definitive [official] list of queries used for badges on there?

There is no official list.

Is there a way to distinguish/quickly find queries used by SE itself and not user-contributed queries?

SEDE is a dataset for users to browse—Stack Exchange uses an internal database which is not connected to SEDE. The data in SEDE dumped monthly from the database, but queries and other database information is not included (and some information, like vote records, is removed).

Keep in mind that some of the badges depend on information that is not available in the data dump (like sportsmanship, talkative and so on)