Comparing counts of two date ranges in Postgres

Question

I am trying to compare two date ranges from a single PostgreSQL 9.6.5 table. The two date ranges are the same length as each other and are in the style of this week versus last week, this month versus last month, last 180 days versus previous 180 days. The table captures customer interactions, each row recording a single interaction - date, start_time, stop_time, location, etc.

I have managed to construct the SQL for a single date range.

SELECT 
  to_char(date, 'day') as day,
  extract(dow from date) as dow,
  count(*) AS curr_count
FROM schema.table
WHERE (date between :date_start AND :date_stop) 
GROUP BY day, dow
ORDER BY dow ASC

With the start_date set to '2018-08-08' and the stop_date set to '2018-08-15', I get this rescordset:

Array ( 
 [0] => Array ( [day] => monday [0] => monday [dow] => 1 [1] => 1 [curr_count] => 78 [2] => 78 ) 
 [1] => Array ( [day] => tuesday [0] => tuesday [dow] => 2 [1] => 2 [curr_count] => 75 [2] => 75 ) 
 [2] => Array ( [day] => wednesday [0] => wednesday [dow] => 3 [1] => 3 [curr_count] => 62 [2] => 62 ) 
 [3] => Array ( [day] => thursday [0] => thursday [dow] => 4 [1] => 4 [curr_count] => 68 [2] => 68 ) 
 [4] => Array ( [day] => friday [0] => friday [dow] => 5 [1] => 5 [curr_count] => 81 [2] => 81 ) 
 [5] => Array ( [day] => saturday [0] => saturday [dow] => 6 [1] => 6 [curr_count] => 3 [2] => 3 ) 
)

I can calculate the dates for the previous period (2018-08-01 to 2018-08-07) easily enough but I don't understand how to add these into the SQL to get the desired result. The end result I would like is:

array (
 array (day, dow, curr_count, prev_count)
 array (day, dow, curr_count, prev_count)
 array (day, dow, curr_count, prev_count)
 array (day, dow, curr_count, prev_count)
)

Any help is greatly appreciated.

Answer 1

Let me assume that you have two sets of parameters, defining the two periods that you want to compare:

with p1 as (
      select date::date as date,
             extract(dow from date) as dow,
             count(*) AS curr_count,
             row_number() over (order by date::date) as seqnum
      from schema.table
      where date between :date_start_1 and :date_stop_1
      group by date::date
     ),
     p2 as (
      select date::date as date,
             extract(dow from date) as dow,
             count(*) AS curr_count,
             row_number() over (order by date::date) as seqnum
      from schema.table
      where date between :date_start_2 and :date_stop_2
      group by date::date
     )
select p1.*, p2.curr_count as prev_count
from p1 join
     p2
     on p2.seqnum = p1.seqnum;

I changed the first column to an explicit date. I doubt you really just want the day of the month and day of the week to key your result set (of course, you can adjust the logic if that is what you really intend).