Using Data.SE, is there a way to tally up the total amount of users on a site?
I wanted to know how many users were on Anime.SE using the query tool.
Meta Stack Exchange is a question and answer site for meta-discussion of the Stack Exchange family of Q&A websites. It only takes a minute to sign up.Sign up to join this community
I hope that I don't disappoint you but the number should be 967 as your query doesn't take into account the users that should already have been deleted as explained here.
-- all databases declare db_c cursor for select [name] from sys.databases where database_id > 5 -- skip master, temp, model, msdb, Data.SE and database_id <> db_id() -- skip current db as well declare @db_c_name sysname -- holds name of db after fetch declare @sql nvarchar(max) -- holds build up sql string -- result table create table #all_accounts ( accountid int) -- create a union of all accountid's across -- all databases set @sql = N'insert into #all_accounts ' open db_c fetch next from db_c into @db_c_name while(@@FETCH_STATUS = 0) begin set @sql = @sql + N'select accountid from ' + QUOTENAME(@db_c_name) + N'.dbo.users' fetch next from db_c into @db_c_name IF (@@FETCH_STATUS = 0) BEGIN set @sql = @sql + N' union ' END end close db_c deallocate db_c print @sql exec(@sql) -- execute the insert with the unions select count(*) as [number of users] from [users] u -- https://meta.stackexchange.com/questions/146450/delete-old-unloved-users/146452#146452 where u.lastaccessdate > dateadd(m, -6, getdate()) -- active in last 6 months ago and u.reputation > 1 -- more then 1 rep and exists (select 1 from posts where owneruserid = u.id) -- visible post and exists (select 1 from #all_accounts where accountid = u.accountid) --other accounts in the network drop table #all_accounts
Wow, that was quite easy... I came up with a simple query to find the amount of users:
SELECT COUNT(Id) FROM Users
A more specific query could entail using the
WHERE keyword in SQL. For instance, to get the amount of users that are of the age 14:
SELECT COUNT(Id) FROM Users WHERE Age=14