Q&A about the site for gardeners and landscapers
-- a partir de http://data.stackexchange.com/es/query/617133/top-voters -- TOP: TOP "cantidad de usuarios devueltos (por mayor reputación)" -- ActivoHace: Activo hace "La última conexión del usuario fue hace menos de (n|0) días" -- RepMin: Mínima reputación "Reputación absoluta (Rep + recompensas ofrecidas) mínima del usuario" -- RepMax: Máxima reputación "Reputación absoluta (Rep + recompensas ofrecidas) máxima del usuario (0 para no limitar)" -- PostsMin: Mínimo publicaciones "Cantidad mínima de publicaciones realizadas por el usuario" -- VotosMin: Mínimo de votos "Cantidad mínima de votos emitidos por el usuario (positivos + negativos)" -- UVMin: Mínimo de votos + "Cantidad mínima de votos positivos emitidos por el usuario" -- DVMin: Mínimo de votos - "Cantidad mínima de votos negativos emitidos por el usuario" -- VRepMin: Mínimo de V/Rep "Mínimo % de votos / reputación absoluta" -- VRepMax: Máximo de V/Rep "Mínimo % de votos / reputación absoluta (0 para no limitar)" -- VdayMin: Mínimo de V/día "Mínima cantidad de votos por día" -- VdayMax: Máximo de V/día "Mínima cantidad de votos por día (0 para no limitar)" DECLARE @TOP int = ##TOP:int?500##, @ActivoHace int = ##ActivoHace:int?90##, @RepMin int = ##RepMin:int?200##, @RepMax int = ##RepMax:int?0##, @PostsMin int = ##PostsMin:int?25##, @VotosMin int = ##VotosMin:int?0##, @UVMin int = ##UVMin:int?0##, @DVMin int = ##DVMin:int?0##, @VRepMin int = ##VRepMin:int?0##, @VRepMax int = ##VRepMax:int?0##, @VdayMin int = ##VdayMin:int?0##, @VdayMax int = ##VdayMax:int?0##; with NumPosts as ( select [OwnerUserId], SUM(CASE WHEN PostTypeId=1 THEN 1 ELSE 0 END) as [#Q], SUM(CASE WHEN PostTypeId=2 THEN 1 ELSE 0 END) as [#A] from Posts group by [OwnerUserId] ), Bounties as ( select [UserId], sum(BountyAmount) as [BountyRep] from Votes where [UserId] != -1 group by [UserId] ), Votes as ( select [Id], [DisplayName], DATEDIFF(DAY, [LastAccessDate], GETDATE()) as [Seen], [Reputation] + ISNULL([BountyRep],0) as Rep, ROUND(CAST(([UpVotes] + [DownVotes]) as float) / NULLIF(DATEDIFF(DAY, [CreationDate], GETDATE()),0),2) as [V/day], [UpVotes] [#UV], [DownVotes] [#DV], ([UpVotes] + [DownVotes]) as [#V], ROUND(((CAST([DownVotes] as float) / NULLIF(CAST(([UpVotes] + [DownVotes]) as float),0)) * 100), 2) as [%DV], [#Q], [#A], ROUND(((CAST([UpVotes] as float) / NULLIF(CAST(([Reputation] + ISNULL([BountyRep],0)) as float),0)) * 100), 2) as [UV/Rep], ROUND(((CAST([DownVotes] as float) / NULLIF(CAST(([Reputation] + ISNULL([BountyRep],0)) as float),0)) * 100), 2) as [DV/Rep], ROUND(((CAST(([UpVotes] + [DownVotes]) as float) / NULLIF(CAST(([Reputation] + ISNULL([BountyRep],0)) as float),0)) * 100), 2) as [V/Rep], [UpVotes] * 10 - [DownVotes] * 2 + ISNULL([BountyRep],0) as [Given], ROUND(((CAST(([UpVotes] * 10 - [DownVotes] * 2 + ISNULL([BountyRep],0)) as float) / NULLIF(CAST(([Reputation] + ISNULL([BountyRep],0)) as float),0)) * 100), 2) as [Given/Earnt], ROUND(CAST(([UpVotes] + [DownVotes]) as float) / NULLIF([#Q] + [#A],0) * 100,2) [V/Posts] from Users u left join NumPosts p on u.Id = p.OwnerUserId left outer join Bounties b on u.Id = b.UserId where [Reputation] >= @RepMin and ([Reputation] >= @RepMax or @RepMax = 0) and [UpVotes] + [DownVotes] >= @VotosMin and [UpVotes] >= @UVMin and [DownVotes] >= @DVMin ), VotosFiltrados as ( select top (@TOP) [Rep], 'site://users/' + CAST([Id] as varchar(20)) + '|' + CAST([Id] as varchar(9)) as [Id], LOWER([DisplayName]) as [Ususario], -- [Seen] as [Activo], [V/day] as [#V/día], [#UV] as [#V+], [#DV] as [#V-], [#V], [%DV] as [%V-], [Given] as [Otorgó], [Given/Earnt] as [Otorgó/Rep], [#Q] as [#P], [#A] as [#R], [UV/Rep] as [%V+/Rep], [DV/Rep] as [%V-/Rep], [V/Rep] as [%V/Rep], [V/Posts] as [%V/Publicaciones] from Votes where (@ActivoHace = 0) or ([Seen] <= @ActivoHace) and [#Q] + [#A] >= @PostsMin and [V/Rep] >= @VRepMin and ([V/Rep] <= @VRepMax or @VRepMax = 0) and [V/day] >= @VdayMin and ([V/day] <= @VdayMax or @VdayMax = 0) order by [Rep] desc ) select * from VotosFiltrados order by [#V] desc