SQL: counting unique votes with a limited number of votes per hour

Question

Given the table of votes (users vote for the choice and must specify an email address):

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

What is the best way to count the “unique” votes (a user who is a unique combination of email + ip), given the restriction they can only vote on twice an hour?

You can count the number of hours between the first and last vote and determine the maximum number of votes allowed for this timeframe, but this allows users to compress all their votes, say a one-hour window and still have them counted.

I understand that anonymous voting on the network is inherently wrong, but I'm not sure how to do this with SQL. Should I use an external script or something else? (For each choice, for each pair of email + ip, get a vote, calculate the next + 1st time stamp, count / cancel / count votes, go to the next hour, etc.)

Answer 1

Sort of

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

If I understand correctly

Answer 2

You can rewrite the insert statement so that only voices can 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 did not specify which SQL language you are using, so this is in SQL Server 2005.

Answer 3

, :

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. , , :

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 
  )