1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited mar 20 18

created may 13 17 Mariano