1. 3
  1.  

  2. 1
    WITH bounds AS (
      SELECT
        (?)::date as lower,
        (?)::date as upper,
        (SELECT count(*) FROM somewhere WHERE something) as total --- XXX
    ),
    daystates AS (
      SELECT
        generate_series(
          greatest( bounds.lower, s.begin ),
          least(    bounds.upper, s.until ),
          '1 day'::interval
        )::date as day,
        s.state,
        count(s.state) as count
      FROM
        schedule s, bounds
      WHERE
            s.type = 1
        AND daterange( s.begin::date, s.until::date, '[]')
            &&
            daterange( bounds.lower,  bounds.upper,  '[]')
      GROUP BY
        day, s.state
    )
    SELECT
      jsonb_object_agg(d.day, d.states) as calendar
    FROM (
      SELECT
        ds.day,
        (
          jsonb_build_object(
            'count', bounds.total - sum(ds.count),
            'state', 'available'
          )
          ||
          jsonb_agg((SELECT r FROM (SELECT ds.count, ds.state) r) ORDER BY ds.state)
        ) as states
      FROM daystates as ds, bounds
      GROUP BY ds.day
    ) as d
    ;