Running a complex query for every date in a range

I've got a table of orders

Column   |            Type             |                      Modifiers                      
------------+-----------------------------+-----------------------------------------------------
 id         | integer                     | not null default nextval('orders_id_seq'::regclass)
 client_id  | integer                     | not null
 start_date | date                        | not null
 end_date   | date                        | 
 order_type | character varying           | not null

The data has non overlapping standing orders for a client_id and occasionally a temporary order that overrides the standing order on it's start_date when they have a matching client_id. There is application level constraints keeping orders of the same type from overlapping.

id | client_id | start_date |  end_date  | order_type 
----+-----------+------------+------------+------------
 17 |        11 | 2014-02-05 |            | standing
 18 |        15 | 2014-07-16 | 2015-07-19 | standing
 19 |        16 | 2015-04-01 |            | standing
 20 |        16 | 2015-07-18 | 2015-07-18 | temporary

For example, on 2015-07-18 client 16 has order #20 as it's active order because it overrides the standing order #19. With some fuss I found an efficient way of querying for active order id's on a date.

SELECT id from (
      SELECT
        id,
        first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
      FROM orders
      WHERE start_date = ?)
    ) active_orders
    WHERE id = active_order_id

If you query this with 2015-07-18 as the placeholders, you would get

id 
----
 17
 18
 20

The query plan on this query compared to some of my other ideas (like sub queries counting the number of temporary orders for a client on a date) is quite small and I'm pretty happy with it. (the design of the table, I'm not thrilled about)

Now, I need a to find all the active orders for a date range joined wi

I would use select distinct on instead of window function, then just join the days.

select 
    distinct on (date, client_id) date, 
    id 
from orders
inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date
  on start_date <= date and (end_date is null or date <= end_date)
order by date, client_id, order_type desc

http://sqlfiddle.com/#!15/5a420/16/0

I can elaborate more if something is not clear.

StackExchange Database Administrators Q#108623, answer score: 5