Agregate SQL query with maximun number of rows to take into account

Question

My head is hurting trying to figure out a SQLquery.

I have a table of vote data with team_id, ip_address and date_voted fields and I need to return a count of votes for each team_id but only count the first 10 rows per IP address in any 24 hour period.

Any help will be greatly appreciated and may stop my head from pounding!

Answer 1

Hmmm... it would be helpful to know what "first" means in this case. Earliest in the day? Random? If there are 12 votes for a given day how do you pick 2 to not count?

Answer 2

Haven't had time to check, but the following should do the trick.

SELECT Yr, DoY, team_id, SUM(IF NbVote < 10, NbVote, 10) As FilteredVoteCount
FROM (
  SELECT YEAR(date_voted) AS Yr, DAYOFYEAR(date_voted) AS DoY, 
    team_id, 
    ip_address,
    COUNT(*) AS NbVotes
  FROM myTable
  -- WHERE here for some possible extra condition.
  GROUP BY YEAR(date_voted), DAYOFYEAR(date_voted), team_id, ip_address
)
GROUP BY Yr, DoY, team_id
ORDER BY Yr, DoY, team_id   -- or some other order may be desired.

Answer 3

Assumption: Only the first ten votes for a team (each row in the votes table is a vote for team_id) from a given IP address should count for a given date.

So here's the raw votes per team per day.

select team_id, vote_date, ip_address, count(*) as raw_vote_count
  from votes
 group by team_id, vote_date, ip_address

Now, using that, correct the number of votes down to no more than ten with a CASE expression:

select team_id, vote_date, ip_address,
       case when raw_vote_count > 10 
            then 10 
            else raw_vote_count 
        end as adjusted_vote_count
  from (select team_id, vote_date, ip_address, count(*) as raw_vote_count
          from votes
         group by team_id, vote_date, ip_address
       ) sub1

If you then want a total votes by day, it's:

select team_id, sum(adjusted_vote_count)
  from (
       select team_id, vote_date, ip_address,
              case when raw_vote_count > 10 
                   then 10 
                   else raw_vote_count 
               end as adjusted_vote_count
         from (select team_id, vote_date, ip_address, count(*) as raw_vote_count
                 from votes
                group by team_id, vote_date, ip_address
              ) sub1
       )
 where date = :mydate
 group by team_id
 order by team_id