SELECT * FROM statuses s ;

SELECT * FROM incident_table it ORDER BY "timestamp" ;

SELECT ts.DATE,
(
    SELECT COUNT(*)
    FROM incident_table it
    INNER JOIN statuses s ON s.id = it.status_type
    WHERE s.label = 'new' AND it.id = (
        SELECT sit.id
        FROM incident_table_table AS sit
        WHERE sit.task_id = it.task_id AND DATE(sit.TIMESTAMP) <= ts.DATE
        ORDER BY sit.TIMESTAMP DESC, sit.id DESC LIMIT 1
        )
) AS number_of_new,

(
    SELECT COUNT(*)
    FROM incident_table it
    INNER JOIN statuses s ON s.id = it.status_type
    WHERE s.label = 'completed' AND it.id = (
        SELECT sit.id
        FROM incident_table_table AS sit
        WHERE sit.task_id = it.task_id AND DATE(sit.TIMESTAMP) <= ts.DATE
        ORDER BY sit.TIMESTAMP DESC, sit.id DESC LIMIT 1
        )
) AS number_of_completed
FROM (
    SELECT DISTINCT DATE("timestamp") AS DATE
    FROM incident_table
    WHERE DATE("timestamp") > DATE((SELECT MAX("timestamp") FROM incident_table), '-30 day')
    ORDER BY DATE ASC
) AS ts;