Take the 2-minute tour ×

I'm trying to count the number of actions per account over a period of time. The period of time may be variable. The way I've thought of doing this is by creating a table for each time period, containing records for each account that get upserted on user action. For example, I'd have a table named '12-05-2013' which would contain a record w/ the following attributes: { "account_id" = uniqueid12345, "uploads" = 4 } The table would basically represent the uploads by all users on that specific day.

I want to be able to find all accounts that had < 2 uploads over the last seven days. This would require me to query over the 7 tables representing the last 7 days. (IE 12-05-2013, 12-04-2013, 12-03-2013, etc). So far I've been unable to find a method of selecting out of these specific tables. Am I modeling the problem wrong? Is there an easy way to do this? I'm new to relational databases so go easy on me :)

share|improve this question
You definitely don't want multiple tables based on date. Make one table that has a DATE field, then you can use the date field to get what you're after, as shown in the answer below. –  Goat CO Jun 3 '13 at 21:44

1 Answer 1

up vote 0 down vote accepted

Why wouldn't you use a query such as this?

select userid, count(*) as NumActions
from ActionTable
where ActionDate bewteen @date1 and @date2;
share|improve this answer
HAVING COUNT(*) < 2 –  Goat CO Jun 3 '13 at 21:45
@Goat_CO . . . I was reacting to the first line in the question sd opposed to the refinement: "I'm trying to count the number of actions per account over a period of time." I'm really curious why this query doesn't work -- seems much simpler than what the OP is suggesting. –  Gordon Linoff Jun 3 '13 at 21:50
Right-o, I agree, am assuming it's lack of familiarity rather than an actual need for that db structure. –  Goat CO Jun 3 '13 at 21:58
Yeah, this seems to be the right way to go- I'll try it out. Thanks! I was trying to follow the instructions here: docs.aws.amazon.com/redshift/latest/dg/… but it seems that was misguided. –  abu Jun 3 '13 at 22:08

Your Answer


