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

<pre><code>votes
--
id: int
choice: int
timestamp: timestamp
ip: varchar
email: varchar
</code></pre>

<p>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 <em>twice</em> per hour?</p>

<p>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.</p>

<p>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...)</p>

[{'answer_id': 71430, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': 2, 'selected': False, 'text': '<p>Something like </p>\n\n<pre><code>select email, ip, count(choice)\nfrom votes\ngroup by email, ip, datepart(hour, timestamp)\n</code></pre>\n\n<p>If I understand correctly</p>\n'}, {'answer_id': 71489, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 0, 'selected': False, 'text': "<p>You could rewrite your insert statement to only allow votes to be inserted based on your contrainsts:</p>\n\n<pre><code>Insert Into Votes\n(Choice, Timestamp, IP, Email)\nSelect\nTop 1\n@Choice, @Timestamp, @IP, @Email\nFrom\nVotes\nWhere\n(Select Count(*) From Votes Where\n    IP = @IP\n    and Email = @Email\n    and Timestamp &gt; DateAdd(h, -2, GetDate())) &lt; 3\n</code></pre>\n\n<p>You didn't mention which SQL language you were using so this is in SQL Server 2005.</p>\n"}, {'answer_id': 74262, 'author': 'AJ.', 'author_id': 7211, 'author_profile': 'https://Stackoverflow.com/users/7211', 'pm_score': 0, 'selected': False, 'text': '<p>I think this would do it: </p>\n\n<pre><code>SELECT choice, count(*) \nFROM votes v \nWHERE \n  ( SELECT count(*) \n    FROM   votes v2\n    WHERE  v.email = v2.email \n    AND    v.ip    = v2.ip \n    AND    v2.timestamp BETWEEN dateadd(hour, -1, v.timestamp) AND v.timestamp \n  ) &lt; 2 \n</code></pre>\n\n<p>FYI, To count votes where users can only vote once per hour, we could do this: </p>\n\n<pre><code>SELECT choice, count(*) \nFROM votes v \nWHERE NOT EXTISTS \n  ( SELECT * \n    FROM   votes v2\n    WHERE  v.email = v2.email \n    AND    v.ip    = v2.ip \n    AND    v2.timestamp BETWEEN dateadd(h,v.timestamp,-1) AND v.timestamp \n  ) \n</code></pre>\n'}]
['https://Stackoverflow.com/questions/71413', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']