SQL: Counting unique votes with a rolling votes-per-hour limit

Question

Given a table of votes (users vote for a choice, and must supply an email address):

votes
--
id: int
choice: int
timestamp: timestamp
ip: varchar
email: varchar

What's the best way to count "unique" votes (a user being a unique combination of email + ip) given the constraint they may only vote twice per hour?

It's possible to count the number of hours between first and last vote and determine the maximum number of allowed votes for that timeframe, but that allows users to compress all their votes into say, a single hour-long window, and still have them counted.

I realize anonymous online voting is inherently flawed, but I'm not sure how to do this with SQL. Should I be using an external script or whatever instead? (For each choice, for each email+ip pair, get a vote, calculate the next +1h timestamp, count/discard/tally votes, move on to the next hour, etc...)

Answer 1

Something like

select email, ip, count(choice)
from votes
group by email, ip, datepart(hour, timestamp)

If I understand correctly

Answer 2

You could rewrite your insert statement to only allow votes to be inserted based on your contrainsts:

Insert Into Votes
(Choice, Timestamp, IP, Email)
Select
Top 1
@Choice, @Timestamp, @IP, @Email
From
Votes
Where
(Select Count(*) From Votes Where
    IP = @IP
    and Email = @Email
    and Timestamp > DateAdd(h, -2, GetDate())) < 3

You didn't mention which SQL language you were using so this is in SQL Server 2005.

Answer 3

I think this would do it:

SELECT choice, count(*) 
FROM votes v 
WHERE 
  ( SELECT count(*) 
    FROM   votes v2
    WHERE  v.email = v2.email 
    AND    v.ip    = v2.ip 
    AND    v2.timestamp BETWEEN dateadd(hour, -1, v.timestamp) AND v.timestamp 
  ) < 2

FYI, To count votes where users can only vote once per hour, we could do this:

SELECT choice, count(*) 
FROM votes v 
WHERE NOT EXTISTS 
  ( SELECT * 
    FROM   votes v2
    WHERE  v.email = v2.email 
    AND    v.ip    = v2.ip 
    AND    v2.timestamp BETWEEN dateadd(h,v.timestamp,-1) AND v.timestamp 
  )