Comparing 2 time periods in SQL for the same groups

Question

I'm trying to aggregate some data, order it by a specific column, then compare the same data with another time period (e.g. the last week).

An example would be, websites with the most hits this week:

url            total hits
www.a.com      10,000
www.b.com      9,0000
www.e.com      5,000

This is ordered by hits, but I'd like to get the hits for the same urls, but over a different time period e.g.:

url            total-hits   total-hits (last week)
www.a.com      10,000       8,000
www.b.com      9,0000       6,000
www.e.com      5,000        6,000

The table layout for this data is a list of page-hits e.g.:

hit_table:

id     timestamp     url
1      1426470088    www.a.com
1      1426470000    www.b.com
1      1426468015    www.c.com
1      1426467000    www.b.com
....

Is it possible to do this in a single SQL query, or do I need to have 2 separate queries for this?

You should add sample data, as well as desired results. Or at least a table layout. — Gordon Linoff, Mar 16 '15 at 01:31
Oops, added a few rows of data. It's essentially just a list of page views. — J3Y, Mar 16 '15 at 01:43
Using Postgres 9.3.1, but it looks like we're already using some SQLAlchemy for some query generation. Although there's no problem with using explicit SQL queries for some cases if needed. — J3Y, Mar 16 '15 at 01:54
In Postgres 9.4 you could use the new aggregate FILTER clause. This question seems very similar: http://stackoverflow.com/questions/29062193/calculate-the-sum-of-a-field-filtered-by-a-window-defined-on-another-field/29068257 — Erwin Brandstetter, Mar 16 '15 at 01:55

Gordon Linoff · Answer 1 · 2015-03-16T01:45:52.097

You can probably do what you want using conditional aggregation. Your question is very vague on the structure of the table, but here is the idea:

select url,
       sum(case when timestamp >= current_date - interval '7 day' then 1 else 0 end) as ThisWeek,
       sum(case when timestamp >= current_date - interval '14 day' and
                     timestamp < current_date - interval '7 day'
                then 1 else 0 end) as ThisWeek
from table t
group by url;

Comparing 2 time periods in SQL for the same groups

1 Answers1