Proportional division by dates

proportional_division.sql

CREATE TEMPORARY TABLE costs (
    code  varchar,
    cost  integer,
    start_at  date,
    end_at    date
);
INSERT INTO costs VALUES
    ('A', 100, '2016-01-01', '2016-01-01'),
    ('B', 100, '2016-01-02', '2016-01-02'),
    ('C', 300, '2016-01-01', '2016-01-03'),
    ('D', 100, '2016-01-04', '2016-01-05')
;

-- Proportional division
WITH RECURSIVE dates(d) AS (
    VALUES (date('2016-01-01'))  -- query.start_at
    UNION ALL
    SELECT date(d, '+1 day')  -- sqlite
    -- SELECT (d::date + 1::integer)  -- postgresql
    FROM dates
    WHERE d < date('2016-01-05')  -- query.end_at
),
date_codes AS (
    SELECT code, cost, d
    FROM costs c JOIN dates ON start_at <= dates.d AND dates.d <= end_at
),
date_counts AS (
    SELECT code, COUNT(d) AS date_count
    FROM date_codes
    GROUP BY code
),
date_costs AS (
    SELECT t.code, cost/date_count AS date_cost, d
    FROM date_codes t JOIN date_counts c ON t.code = c.code
    ORDER BY t.code, d
)
SELECT
    code, SUM(date_cost) AS cost
FROM
    date_costs
WHERE
    date('2016-01-01') <= d AND d <= date('2016-01-04')
GROUP BY
    code
ORDER BY
    code
;

/* PostgreSQL 9.5 
CREATE TABLE
INSERT 0 4
 code | cost
------+------
 A    |  100
 B    |  100
 C    |  300
 D    |   50
(4 rows)
*/

/* sqlite3.8
code        cost
----------  ----------
A           100
B           100
C           300
D           50
*/